CODEWITHSUNDEEP
databasems-accessrdbms
EnsAndBees
EnsAndBees

Reputation: 73

Multiple fields in one sheet mapping to single field in another sheet

I need help designing the following architecture. Say Table 1 has a list of companies along with their information. Then Table 2 has a list of "primary" companies, followed by multiple fields denoting the companies that they do business with ("secondary" companies). So the first field in Table 2 is the "primary" company, and then there could follow up to 20 columns, each with a single entry of a company that does business with the "primary company". Each of those companies are listed in Table 1. I want to be able to tie the information about those "secondary companies" in with the information about them in Table 1. For example, I want to be able to see how many "secondary" companies in Table 2 are from California. I each company listed once in Table 1 along with headquarters locations; but each company in Table 2 is in a distinct column. I'm just getting confused about how to structure this, because when I try to make a query relationship between Table 1 and Table 2, I end up making a join between "Company" in Table 1 and every column in Table 2. Not good, right?

I have something like this...

Table 1

Company  City         State
A        Los Angeles  CA
B        San Diego    CA
C        New York     NY
.
.
.

Table 2
Primary Company   Secondary1 Secondary2 Secondary3 Secondary4
A                 B          C          X          Y
B                 A          C          Z          W
C                 A          B          W          X

Do I need another table for this? Should I just concatenate these 20+ secondary company fields into one column somehow? Any direction you could give would be very helpful.

Thank you!

Upvotes: 0

Views: 44

Answers (1)

Mark_Eng
Mark_Eng

Reputation: 503

I think what you want is this:

Table 1

Company  City         State
A        Los Angeles  CA
B        San Diego    CA
C        New York     NY
.
.
.

Table 2
Primary Company   Secondary
A                 B          
A                 C          
A                 X
A                 Y
B                 A
B                 C
B                 Z
B                 W

...

In this case you will have a relationship between table1, column1 and table2 column 1 and 2. This is acceptable (though technically better if you use numbers instead of letters).

Upvotes: 3

Related Questions