list of records that DON'T appear in another table (1 Viewer)

iankerry

Registered User.
Local time
Today, 08:06
Joined
Aug 10, 2005
Messages
190
Hi Guys

I have been trying to work this out using posts from previous queries, but i cant seem to fathom it out.

i have a table called Venues and a table called EventsFlicks.

they are connected by fields Venues.ID and EventsFlicks. VenueID.

What I would like to have is a list of venues that haven't booked any screenings yet. e.g. they will not have an entry in EventsFlicks for the coming period.

Could someone push me in the right direction please?

Many thanks

Ian
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:06
Joined
Aug 30, 2003
Messages
36,118
Check out the unmatched query wizard. You might need to use a query that finds everybody that has an entry in the desired time frame.
 

Mark_

Longboard on the internet
Local time
Today, 01:06
Joined
Sep 12, 2017
Messages
2,111
I am guessing you already know how to make ONE query that returns all venues that have bookings during a period.

You can create a second query that joins to the first. You can include most any field, but you may find it most helpful if it is the same as one of the fields you will be using, just to make sure you have something you can compare to ensure it is working proper.

Then you exclude all records that have the field from your first query!
 

MarkK

bit cruncher
Local time
Today, 01:06
Joined
Mar 17, 2004
Messages
8,178
Do an outer join, group by the parent rows, and count the child rows. Where the child row count is zero, those are the rows you want, so SQL like...
Code:
SELECT v.VenueID, v.Name
FROM EventFlicks AS ef RIGHT JOIN Venues AS v ON ef.VenueID = v.VenueID
GROUP BY v.VenueID, v.Name 
HAVING Count(ef.ID) = 0;
hth
Mark
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:06
Joined
May 21, 2018
Messages
8,463
The tutorial that Jdraw recommends is probably the most efficient using a left join. That is the same as the unmatched wizard. The other common way is a not in select query, but subqueries in Access are usually much slower.

Code:
SELECT * FROM Venues where Venue.ID NOT IN (SELECT VenueID FROM EventsFlick)
 

iankerry

Registered User.
Local time
Today, 08:06
Joined
Aug 10, 2005
Messages
190
Thanks folks, a few pointers there - will have a go now and see what i can do... i have tried the unmatched query wizard, but it didnt provide the expected results!

Am realising the query is going to need to be more complex - e.g. i need to see if a venue is A: a current venue and B: that they havent booked a future date.

but am off to give it a go.

thanks again,

ian
 

iankerry

Registered User.
Local time
Today, 08:06
Joined
Aug 10, 2005
Messages
190

Hi jdraw,

this tutorial seems to be excluding records that one has typed into a table. rather than looking a two tables and providing a list of venues form one table that dont appear in the other.

is that right or have i missed something? I am not much of a query designer so i liked the idea of a tutorial!

ta

ian
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:06
Joined
May 7, 2009
Messages
19,169
if you are looking for Venues that is Not in EventFlicks, use Left Join:

Select Venues.ID From Venues Left Join EventFlicks On Venues.ID = EventFlicks.VenueID Where EventFlicks.VenueID Is Null;
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 01:06
Joined
Aug 30, 2003
Messages
36,118
if you are looking for Venues that is Not in EventFlicks, use Left Join:

Select Venues.ID From Venues Left Join EventFlicks On Venues.ID = EventFlicks.VenueID Where EventFlicks.VenueID Is Null;

That's what the unmatched query wizard would produce.

Ian, like I said you may want to use a query for one or both sides instead of the tables themselves, but I think the wizard will give you the result you want.
 

iankerry

Registered User.
Local time
Today, 08:06
Joined
Aug 10, 2005
Messages
190
if you are looking for Venues that is Not in EventFlicks, use Left Join:

Select Venues.ID From Venues Left Join EventFlicks On Venues.ID = EventFlicks.VenueID Where EventFlicks.VenueID Is Null;

when i use this code - and add a date filter so i only look at future dates - it brings back zero results.

Code:
SELECT dbo_Venues.ID, dbo_Venues.VENUE, dbo_EventsFlicks.datefield
FROM dbo_Venues LEFT JOIN dbo_EventsFlicks ON dbo_Venues.ID = dbo_EventsFlicks.VenueID
WHERE (((dbo_EventsFlicks.VenueID) Is Null) AND ((dbo_EventsFlicks.datefield)>#1/9/2018#));


and yet there are 3 venues that haven't booked. Am I missing something?

thanks
 

Minty

AWF VIP
Local time
Today, 08:06
Joined
Jul 26, 2013
Messages
10,353
Because you have added an AND with a date value, you are now excluding the null date values.

You need to add something like
AND (dbo_EventsFlicks.datefield is Null OR dbo_EventsFlicks.datefield >#1/9/2018#)
 

Users who are viewing this thread

Top Bottom