ask yourself if a company can have more than one account and an account can have more than one company. If yes to both questions you need a join table.
The answer to both is "yes".
I'm struggling with how to structure that join to bring the single record with multiple fields in it I want to pull out. In that base record I have "Account Name", "ID Code", "Submitted company A", "Result company A", "Submitted company B", "Result company B", etc. etc.
I've tried a range of joins and can't seem to wrap my head around the answer.
The answer to both is "yes".
I'm struggling with how to structure that join to bring the single record with multiple fields in it I want to pull out.
A join table is joined to two (or more) parent tables. That gives you
Account -< JoinTable >- Company (where one -< many)
Account::id = JoinTable::id_account, and Account::id_company = Company::id
Now you need to decide which fields to transfer.
In that base record I have "Account Name", "ID Code", "Submitted company A", "Result company A", "Submitted company B", "Result company B", etc. etc.
Ask yourself what attributes you will use to describe the child. That is usually quite easy: every field that has an enumeration (in this case, an alphabetical one, but still) is a candidate. Take the generic form of each field and create it in the join table, like
then delete it from the Account table. You also need several fields to maintain your structure: a primary key for the join table, plus two foreign keys, one for each parent:
Join::id (non-empty, unique, auto-enter)
Join::id_account (same data type as the primary key field of Account; must be set when creating a record)
Join::id_company (ditto for Company)
which of course means that you need these primary keys in the parent table. Not sure if yourID Code already is such a key, or if it is a piece of business data.
What you need to figure out at this point is how to facilitate data entry, i.e. assigning companies to an account. If you need help here, don't hesitate to ask.
btw, I'm in the process (late stages, actually) of developing a Fund Management database for a client. Any similarities to your scenario?
Thank you. I think this is the answer, I just need to do more thinking about the structure.
Your fund management solution sounds like it might be similar. I'm in the insurance business and built it starting with Lotus Approach in '95, and then have been upgrading and improving in FM for ten years at least. It's actually been a lot of fun, but I only dig into it periodically when I'm trying to improve reporting, etc. This project is trying to summarize prior results with certain ID codes as a guide to how future similar opportunities should be handled. The info is there in a raw form, and I can get by do in a range of searches, but I'm trying to have it automatically appear whenever a new account is entered based on the ID Code.
Thanks for the help. I may be back for more! Have a great day.
the account may have been sent to (one individual field for each company within the same table), along with the results of that effort. Both of these make up the company data set.
Just to be clear, this part of your original set up is not a good approach. Just consider what happens if you need to send the account to 11 or more companies, let alone that this part defeats your attempt to set up a relationship and a portal to show that data.
This is the data that you need to move into a join table in order to gain the needed flexibility.