I'm trying to write a query that looks up two different IDs from the same ID table, and brings back info from that table. I'm not sure how to do the two-to-one match.
Specifically, I have one table with company info. Each company has two contact people, and the table lists their identifying IDs. I have another table listing all the different contact people (IDs and then names). I'd like to be able to run a query that shows the names of the two contact people for each company. But, that requires two ID fields on the company table (primary and secondary), each being joined with the single ID on the contact table.
Company Table:
Fields: CompanyID
Company Name
PrimaryContactID
SecondaryContactID
Contact Table:
Fields: ContactID
Contact Name
I want to link both the PrimaryContactID and the SecondaryContactID fields from the company table to the ContactID field in the Contact table, so that I can run a query that shows me the names of both contacts for each company. Specifically, I want the query output to include the fields:
CompanyID
CompanyName
PrimaryContactName (which is the name that matches the PrimaryContactID)
SecondaryContactName (which is the name that matches the SecondaryContactID)
This seems like it should be straightforward, and I'd expect that access is able to do things like pull names based on IDs, but I'm not sure how to make it do so here. Simply creating a join with arrows going from both PrimaryContactID and SecondaryContactID to the ID field in the contacts table doesn't work, and, even if it did, I'm not sure how to tell it which name goes in which of my two name fields (primary & secondary) in the query.
Can anyone tell me what I'm doing wrong? Thanks.
Specifically, I have one table with company info. Each company has two contact people, and the table lists their identifying IDs. I have another table listing all the different contact people (IDs and then names). I'd like to be able to run a query that shows the names of the two contact people for each company. But, that requires two ID fields on the company table (primary and secondary), each being joined with the single ID on the contact table.
Company Table:
Fields: CompanyID
Company Name
PrimaryContactID
SecondaryContactID
Contact Table:
Fields: ContactID
Contact Name
I want to link both the PrimaryContactID and the SecondaryContactID fields from the company table to the ContactID field in the Contact table, so that I can run a query that shows me the names of both contacts for each company. Specifically, I want the query output to include the fields:
CompanyID
CompanyName
PrimaryContactName (which is the name that matches the PrimaryContactID)
SecondaryContactName (which is the name that matches the SecondaryContactID)
This seems like it should be straightforward, and I'd expect that access is able to do things like pull names based on IDs, but I'm not sure how to make it do so here. Simply creating a join with arrows going from both PrimaryContactID and SecondaryContactID to the ID field in the contacts table doesn't work, and, even if it did, I'm not sure how to tell it which name goes in which of my two name fields (primary & secondary) in the query.
Can anyone tell me what I'm doing wrong? Thanks.