Access Query Two-to-One field match

[BarbaraT]

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.

 

[alansidman]

I would do it a little differently.

I would not have the contact IDs in the Primary table. Rather I would have two additional fields in the contact table that identifies whether a contact is primary of secondary and a FK field with the company ID. Join your tables in the query on the company IDs. In your query, you can now list your company and the primary and secondary contacts. Create two fields in your query with and expression to identify the contact as primary or secondary.

 

[BarbaraT]

I would do it a little differently.

I would not have the contact IDs in the Primary table. Rather I would have two additional fields in the contact table that identifies whether a contact is primary of secondary and a FK field with the company ID. Join your tables in the query on the company IDs. In your query, you can now list your company and the primary and secondary contacts. Create two fields in your query with and expression to identify the contact as primary or secondary.

Thanks for the response.

Some people are both the primary contact for one company and the secondary contact for another. So, I can't include info on the contact table about whether someone is primary or secondary, because they could be both in different situations. That info needs to be associated with the company.

 

[xenou]

Because you've structured your table with repeating fields (two fields for ContactID) you don't get any easy SQL Next time you won't do that and it will be a piece of cake going forward.

You have to write some extra SQL to workaround this problem in your current table structure. Untested, but this might work. I used a left join on the two inner queries in case of there being a primary contact but no secondary contact for some companies).

SELECT 
        T1.CompanyID,
	T1.CompanyName,
	T1.PrimaryContactName,
	T2.SecondaryContactName
	
	FROM
		(
			SELECT
				a.CompanyName,
				a.CompanyID,
				b.PrimaryContactName
			FROM
				Company a
			INNER JOIN
				Contact b
			ON
				a.PrimaryContactID = b.ContactID
		) T1
		LEFT JOIN
		(
			SELECT
				c.CompanyName,
				c.CompanyID,
				d.PrimaryContactName
			FROM
				Company c
			INNER JOIN
				Contact d
			ON
				c.SecondaryContactID = d.ContactID
		) T2
	
	ON T1.CompanyID = T2.CompanyID

Edit:
Note that what you really need is a linking table:

Company(CompanyID, CompanyName)
Contact(ContactID, ContactName)
Company_Contact(CompanyID, ContactID, PrimaryContact)

Where PrimaryContact identifies whether the contact is the primary one (you could also use something else there, such as ContactType {'Primary','Secondary',[...]}) - the Company_Contact table is uniquely identified by the CompanyID *and* ContactID, so you can have two (or more) contacts for a company, and a contact can belong to more than one company ... but could never be entered twice for the *same* company.

 

[Kreszch68]

I agree the company table is not set up in the most efficient way, but I don't think it's that hard to query this one.

The easy way is: Go to the query designer, drag the company table into the design window and drag the contact table twice to de design window.
Select one contact table and hit ALT + ENTER to open the properties, alias it to PrimaryContact, then go to the other contact table on your screen and do the same, but of course alias this one as SecondaryContact. Now drag the contactID from the PrimaryContact to the corresponding ID in de company table, and drag the contactID of the SecondaryContact to the SecondaryContactID.
Then drag the fields you need from each table to the section where you select the fields. That's all.

If you want, you can tweak the SQL al little by setting the display option to SQL:

SELECT Company.CompanyName, PrimaryContact.ContactName [COLOR=#ff0000][B]AS PrimaryContact[/B][/COLOR], SecondaryContact.ContactName [B][COLOR=#ff0000]AS SecondaryContact
[/COLOR][/B]FROM Contact AS SecondaryContact INNER JOIN (Contact AS PrimaryContact INNER JOIN Company ON PrimaryContact.ContactID = Company.PrimaryContactID) ON SecondaryContact.ContactID = Company.SecondaryContactID;

 

[xenou]

SELECT Company.CompanyName, 
    PrimaryContact.ContactName AS PrimaryContact, 
    SecondaryContact.ContactName AS SecondaryContact
FROM 
    Contact AS SecondaryContact INNER JOIN 
        (Contact AS PrimaryContact 
        INNER JOIN 
        Company ON PrimaryContact.ContactID = Company.PrimaryContactID) 
    ON SecondaryContact.ContactID = Company.SecondaryContactID;

I'm not sure about the SQL here. There's no Company.SecondaryContactID in the inner query so wouldn't there be no match between the two sets we are trying to join? Another problem: the inner query is selecting only primary contact ID's so if there was a customer with a secondary ID that was also used as a primary ID then it would be excluded from the result set because of the inner join. Maybe something with union is needed? Not really sure though - I've not looked extremely closely. You're right that I'm exaggerating the difficulty a little - two repeating fields isn't best but can usually be worked out (I've done it myself, actually).

ξ

 

[Kreszch68]

You're partially right. There is actually no inner query (as in: correlated or synchronized) in the SQL, only a join from the left table on the two aliases of the right table. However, in the SQL from my previous post, a problem will occur when a record in the Company table only has one of the two ID's. So by switching from INNER join to a LEFT join this problem is solved.

The next SQL is tested and works:

SELECT Company.CompanyName, PrimaryContact.ContactName AS PrimaryContact, SecondaryContact.ContactName AS SecondaryContact
FROM (Company LEFT JOIN Contact AS PrimaryContact ON Company.PrimaryContactID = PrimaryContact.ContactID) LEFT JOIN Contact AS SecondaryContact ON Company.SecondaryContactID = SecondaryContact.ContactID;

And just for the fun, an example using Correlated queries to fetch the contacts

SELECT Company.CompanyName, (Select ContactName from Contact Where ContactID = PrimaryContactID) As PrimaryContact, (Select ContactName from Contact Where ContactID = SecondaryContactID) As SecondaryContact
FROM Company

 

[Kreszch68]

I couldn't resist, this is another approach:

SELECT CompanyName, ContactName, 'Yes' As Primary FROM Company INNER JOIN Contact ON Company.PrimaryContactID = Contact.ContactID
UNION 
SELECT CompanyName, ContactName, 'No' As Primary FROM Company INNER JOIN Contact ON Company.SecondaryContactID = Contact.ContactID

 

[xenou]

This looks like a winner from a practical point of view:

The next SQL is tested and works:


Code:

SELECT Company.CompanyName, PrimaryContact.ContactName AS PrimaryContact, SecondaryContact.ContactName AS SecondaryContact
FROM (Company LEFT JOIN Contact AS PrimaryContact ON Company.PrimaryContactID = PrimaryContact.ContactID) LEFT JOIN Contact AS SecondaryContact ON Company.SecondaryContactID = SecondaryContact.ContactID;

It's nice to see a correlated query every now and then though.
ξ