Nested Query with many-to-many relationship

[ajarrell]

I have a db of contacts that includes a linking table to match contacts to the groups each is in. I need to be able to query for contacts that are in a specified group AND in another specified group. My query instead returns all contacts in [SelectGroup2]. Is there a way to make this work? Thanks in advance!

SELECT tblContacts.[FirstName], tblContacts.[LastName], tblContacts.Company, tblContacts.Email, tblContacts.Type, tblGroups.Group

FROM tblGroups INNER JOIN (tblContacts INNER JOIN tblContGrpLink ON tblContacts.ContactID = tblContGrpLink.Contact) ON tblGroups.GroupID = tblContGrpLink.Group

WHERE (((tblGroups.Group)=[Select Group 2])) AND EXISTS
( SELECT tblGroups.Group
FROM tblGroups AS GroupList INNER JOIN (tblContacts INNER JOIN tblContGrpLink ON tblContacts.ContactID = tblContGrpLink.Contact) ON GroupList.GroupID = tblContGrpLink.Group
WHERE (((GroupList.Group)=[SelectGroup1])));

 

[plog]

I need to be able to query for contacts that are in a specified group AND in another specified group

Going off that, I would use a Summation query. You would limit it to the 2 groups you wanted and then return results that have a total of 2 records. Like so:

SELECT ContactID
FROM tblGroups 
WHERE Group="Group 1" OR Group="Group 2"
GROUP BY ContactID
HAVING COUNT(ContactID)=2

The only issue with this is if someone could have 2 records for the same group. In that case you would need a subquery to determine the unique groups a person is in.

 

[ajarrell]

Thanks for your timely response!

I have not allowed records in the link table to have the same person and group more than once.

Now I am getting the message: "Your query does not include the specified expression 'First Name' as part of an aggregate function."

Thanks, again.

 

[plog]

What does your SQL look like? Sounds to me like you have fields in the SELECT that are not in the GROUP BY

 

[ajarrell]

SELECT tblContacts.[ContactID],tblContacts.[First Name], tblContacts.[Last Name], tblContacts.[Company], tblContacts., tblGroups.Group
FROM tblGroups INNER JOIN (tblContacts INNER JOIN tblContGrpLink ON tblContacts.ContactID = tblContGrpLink.Contact) ON tblGroups.GroupID = tblContGrpLink.Group
WHERE (((tblGroups.Group)=[Select Group 1])) OR (((tblGroups.Group)=[Select Group 2]))

GROUP BY ContactID HAVING COUNT(ContactID)=2;

 

[plog]

When you have a GROUP BY clause, every item in the SELECT must either be in the GROUP BY or be acted on by a aggregate function (SUM, COUNT, MAX, etc.). So put every field in the SELECT in the GROUP BY

 

[ajarrell]

That fixed the error.

SELECT tblContacts.[ContactID],tblContacts.[First Name], tblContacts.[Last Name], tblContacts.[Company], tblContacts., tblGroups.Group
FROM tblGroups INNER JOIN (tblContacts INNER JOIN tblContGrpLink ON tblContacts.ContactID = tblContGrpLink.Contact) ON tblGroups.GroupID = tblContGrpLink.Group
WHERE (((tblGroups.Group)=[Select Group 1])) OR (((tblGroups.Group)=[Select Group 2]))

GROUP BY tblContacts.[ContactID],tblContacts.[First Name], tblContacts.[Last Name], tblContacts.[Company], tblContacts.[Email], tblGroups.Group
HAVING COUNT(ContactID)=2;


However, now I get no records. If I leave off the HAVING COUNT(ContactID)=2, I get the appropriate contacts, some twice as expected.

 

[plog]

Can you post your database?

 

[ajarrell]

How do I do that? (Sorry.)

 

[plog]

In the posting box at the bottom, go to 'Go Advanced'. One of the options will be to upload a database.

I'd prefer a slimmed down version--just the tables you are working with--no forms, no reports, etc. Make it easy for me to find what to work with.

 

[ajarrell]

I am selecting groups:
ACPA-SE BOD
ACPA-SE MBR Contractor

Thank you!

 

[plog]

You can't GROUP BY the Group field to get the results you want. Remove that from both the SELECT and the GROUP BY clauses.

 

[ajarrell]

Thank you so much - works like a charm!