Searching for records that don't exist

[rgagarrett]

I'm using Microsoft Access to create my database. In short, my problem
is this: I have three tables: one containing information about players
(1 record per person); one containing information about dates (1 record
per date); and one that holds information about both of these (multiple
records for players and dates). I enter into the third table the date,
playerID and then some information. I need to find all the dates in
this third table for which a playerID does _not_ exist (i.e. those
dates that I haven't entered info about the player yet).

In other words:

Table 1:
PlayerID (primary key)
PlayerName

Table 2:
Date (primary key)
Record2
Record3

Table 3:
Date (related to Date in table 2)
PlayerID (related to PlayerID in table 1)
Available

I need a list of the dates and playerID's that do not occur together in
table 3. Any help and suggestions would be much appreciated - I've not
been able to find anything in my searching on the web.

Thanks.

 

[Allen Browne]

You want to know every combination of PlayerID and Date that does not exist
in Table3?

1. Create a cartesian product of all players and dates
======================================
1.1 Create a query containing Table1 and Table2.
1.2 There will be no line joining the 2 tables in the upper pane of table
design.
1.3 Drag the PlayerID field from Table1 into the grid.
1.4 Drag the Date field from Table2 into the grid.
1.5 Save this table with the name: qryPlayerDate.
1.6 Test, and close the query.
It is the lack of any join that gives every possible combination of the two.

2. Create an unmatched query between this one and Table3
===========================================
2.1 Create another query, and using Table3 and the query you just created as
input tables.
2.2 In the upper pane of query design, drag qryPlayerDate.PlayerID onto
Table3.PlayerID, and qryPlayerDate.Date onto Table3.Date.
2.3 You now see 2 lines joining the 2 tables.
2.4 Double-click the first join line.
2.5 Access gives you a dialog with 3 options.
2.6 Choose the one that says:
All records from qryPlayerDate, and any matches from Table3.
2.7 Okay the dialog, and you see an arrowhead on the join line.
2.8 Repeat this for the other line joining the 2 tables.
2.9 You should now have arrowheads on both lines, pointing the same
direction.
2.10 Drag the primary key from Table3 into the grid.
2.11 In the Criteria row under this field, enter:
Null
2.12 Drag the PlayerID and Date from qryPlayerDate into the grid.

BTW, Date is a reserved word in JET and in VBA, so it is not a good name for
a field. You might like to change it to GameDate or some such before you
build these queries.

 

[rgagarrett]

That's excellent, thanks. It works exactly as I was after. I was
having difficulty getting past the first step (creating the cartesian
product of all players and dates) and from what you're saying it only
works if the two tables aren't joined. Which would be at least part of
the reason I had difficulty! I didn't need that join anyway...

And thanks also for the tip on the Date field, I'll change the name.