Query to get everyone not in table

[rbuick]

Here's what I want to do.

I have table 1 with staff member's firstname, lastname, and id#.
I have table 2 with staff member's entry#, entrydate, and id#.
Relationship between table 1 and table 2 through id# w/ referrential
integrity.

I want to put a query together that I would input a date and retrieve a list
of staff member's who do not have an entry on that date. Basically, I want
to find out who didn't show up to the meeting on that date.

Does anyone have a solution for this?

Robin

 

[Marshall Barton]

I have table 1 with staff member's firstname, lastname, and id#.
I have table 2 with staff member's entry#, entrydate, and id#.
Relationship between table 1 and table 2 through id# w/ referrential
integrity.

I want to put a query together that I would input a date and retrieve a list
of staff member's who do not have an entry on that date. Basically, I want
to find out who didn't show up to the meeting on that date.

That's just a find unmatched records query. The query
wizard will create one for youand you can tweak it for your
date criteria.

 

[John Spencer]

Two query solution.

Query A: WhoWasThere
SELECT Table2.[Id#]
FROM Table2
WHERE EntryDate = #1/1/2007#

QueryB is an unmatched query using queryA and Table1
SELECT Table1.[ID#], FirstName, LastName
FROM Table1 LEFT JOIN WhoWasThere
ON Table1.[ID#] = WhoWasThere.[ID#]
WHERE WhoWasThere.[ID#] is Null

You can do that all in one query
SELECT Table1.[ID#], FirstName, LastName
FROM Table1
WHERE [ID#] NOT IN
(SELECT Table2.[Id#]
FROM Table2
WHERE EntryDate = #1/1/2007#)

The first option may be faster - especially with large data sets.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================