Not in date range query (1 Viewer)

[VygonDBA]

Hi all,
I wonder if someone could help please as I'm stumped?

I'm trying to produce a query that shows all records of patients that have a 'non-active' status (stored in the 'Patient Details' table) and haven't had any deliveries after 31/10/2011 (date stored in the 'Deliveries' table).

I've tried a few different ways including using NOT IN (which access didn't like!) but I'm still no closer to getting the correct records, does anyone have any suggestions?

Many thanks.

 

[namliam]

First make a query that does have deliveries after 10/31/2011 (remember dates are in US formats always, unless its a text column but that has its unique problems too)
Save this query

Now make a new query and add your table and the above query to it, make it a left join (double click on the line and make sure you select the option "All from table and only those from the table that can be matched")
Now add the Key from your query to the query and stick "Is Null" into the Criteria

Good luck

 

[VygonDBA]

Many thanks, I had a feeling I'd be looking at either a left or right join!!
I'll give it a go.
Thanks again.
M