Show remaining records

  • Thread starter Thread starter Andy Davis
  • Start date Start date
A

Andy Davis

I have a table which displays the daily log on's of approximately 20 users.
I want to run a query that when I enter a date shows all the users that WERE
NOT logged on for that particular day.

For example, say user id's 1 and 2 were on the system on a particular day
but user id's 3 - 20 were not. How do I display just the remaining 18 users
without displaying all of their log in details for the remaining dates
included in the table, as currently happens when I run the query using the
Not Like (date) function in the query design grid.

I also have a separate table which lists the 20 users names, user id's and
other miscellaneous data.

Many thanks
 
Andy Davis said:
I have a table which displays the daily log on's of approximately 20
users. I want to run a query that when I enter a date shows all the
users that WERE NOT logged on for that particular day.

For example, say user id's 1 and 2 were on the system on a particular
day but user id's 3 - 20 were not. How do I display just the
remaining 18 users without displaying all of their log in details for
the remaining dates included in the table, as currently happens when
I run the query using the Not Like (date) function in the query
design grid.

I also have a separate table which lists the 20 users names, user
id's and other miscellaneous data.

Many thanks
Click to expand...

The following won't be the most efficient query, but the logic is
straightforward:

*** WARNING: "Air SQL" ***

SELECT tblUsers.*
FROM tblUsers
WHERE tblUsers.UserID NOT IN
(
SELECT tblLogonData.UserID
FROM tblLogonData
WHERE tblLogonData.LogonDate =
[Enter the date you're interested in]
);

Something like this is probably more efficient:

SELECT tblUsers.*
FROM
tblUsers
LEFT JOIN
(
SELECT UserID
FROM tblLogonData
WHERE tblLogonData.LogonDate =
[Enter the date you're interested in]
) As L
ON tblUser.UserID = L.UserID
WHERE L.UserID Is Null;
 
I have a table which displays the daily log on's of approximately 20 users.
I want to run a query that when I enter a date shows all the users that WERE
NOT logged on for that particular day.

For example, say user id's 1 and 2 were on the system on a particular day
but user id's 3 - 20 were not. How do I display just the remaining 18 users
without displaying all of their log in details for the remaining dates
included in the table, as currently happens when I run the query using the
Not Like (date) function in the query design grid.

I also have a separate table which lists the 20 users names, user id's and
other miscellaneous data.

Many thanks
Click to expand...

Try using the "Unmatched Query Wizard".

John W. Vinson[MVP]
(no longer chatting for now)
 
Back
Top