Relationship problem

[Ultradistance2005]

I have a database with each record representing one account. Within each account record, there are ten different companies 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. Also within that single record there is an identifying ID Code, which represents the type of account it is.

 

What I want to do now is create a table with a portal where I can select or enter an ID Code, and get each instance of that ID Code with each individual company data set. So if with the individual account record I noted that I sent it to four of the ten companies, I want it to return four responses, one for each of the companies I sent it to, with the unique responses (also in a field in the main account database) with the details of the individual record.

 

I feel like the answer is right there, but I just can't get it to work. Any suggestions would be much appreciated. Thank you.

[bcooney]

Not sure I follow, but my interpretation is this structure:

Each account has many interactions with one or more companies. During that interaction, there is a result or response recorded. Each account has a type.

AccountType >--Account--< Interaction >--Company

So that each account has one type, many interactions and each interaction is a join record between the Account and a Company. In the Interaction table, I would have fields about that interaction (the response would be one). Perhaps the date of the interaction, etc.

I'd have a portal on an Account form layout to the Interactions.

[Ultradistance2005]

I agree that that makes sense. My problem is I'm working with a database I started over ten years ago, and I've got about 80,000 records with the various company interactions in the single record. I'm trying to avoid re-building that aspect if I can.

I'm struggling with how to structure a join to capture the single record with multiple fields in it I want to pull out, several times. Once for each company response.  In the base record I have "Account Name", "ID Code", "Submitted company A", "Result company A", "Submitted company B", "Result company B", etc. etc. I want to get every instance of matching code where there is a "Submitted company A" along with the result, each with "submitted company B", etc. 

[bcooney]

I see you're getting answers from eroist on the FM Community forum. I'm sure he'll help you out.

[Ultradistance2005]

Thank you.