0

I have a simple many to many relationship set up on access with "List", "DayJunction" (Junction Table) and "Days". I am trying to query for records in "List" that have no related records in "Days".

enter image description here

I have tried the following SELECT query:

enter image description here

and the following COUNT query:

enter image description here

But neither is giving me the desired result. Is it possible to extract these results using just one query? Also why did the previous two not give me the intended results?

1

I'm going to assume for the purpose of this answer that these records refer to days in which a particular company attended something. If that's correct you can do this with two tables instead of three, and it's a one-to-many join if you don't have duplicate company names. It is perfectly ok to have a many-to-many join if your tables are something like this:

List of companies, Companies

Company      

Dell
Apple
Google

List of attendance by company, Attendance

Company       Day_attending

Dell          08/01/2016
Dell          08/31/2016
Apple         08/05/2016
Apple         08/08/2016

So in this case you'd be looking for Google as the output since Google has no attendance records.

Access doesn't know how to do this with the query designer, so you'll have to go into SQL mode and enter the code yourself. If you were in normal SQL you'd use EXCEPT, but Access doesn't support it. Instead, you can do a funky join by Company value where Company is null. Like this:

SELECT c.Company
FROM Companies as c
LEFT JOIN
Attendance as a
    ON c.Company = a.Company 
    WHERE a.Company is null

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.