CODEWITHSUNDEEP
sqlms-access
Evan
Evan

Reputation: 618

SQL query to find list of primary keys not used

I am trying to make a drop down picker in an Access database to display all the primary keys not used, in this case a date that is limited to the first of the month.

I have 2 tables that are for this use

tblReport 
pk date  |  Data for this record |
05/01/13 |     stuff
06/01/13 |     stuff
07/01/13 |     stuff
08/01/13 |     stuff

and

tblFutureDates
pk date  |  an index
05/01/13 |     1
06/01/13 |     2
07/01/13 |     3
08/01/13 |     4
09/01/13 |     5
10/01/13 |     6
11/01/13 |     7
12/01/13 |     8

I want a query that looks at these two tables and returns the dates that are in the second table that aren't in the first one. I have tried some joins but cannot figure it out. This is what I have thus far:

SELECT tblFutureDates.FutureDate
FROM tblFutureDates RIGHT JOIN tblReport 
ON tblFutureDates.FutureDate = tblReport.ReportMonth;

and that returns:

05/01/13
06/01/13
07/01/13
08/01/13

Thanks

Upvotes: 0

Views: 48

Answers (1)

FuzzyTree
FuzzyTree

Reputation: 32402

This selects dates from tblFutureDates that are NOT IN tblReport

SELECT tblFutureDates.FutureDate
FROM tblFutureDates 
WHERE tblFutureDates.FutureDate
NOT IN (SELECT tblReport.ReportMonth FROM tblReport)

You can also use LEFT JOIN WHERE IS NULL and NOT EXISTS for more information about all 3 see this post.

Upvotes: 1

Related Questions