Jump to content

Relationship problem

This topic is 2754 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

This topic is 2754 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.