Users not connected query

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

Andy Davis

I have a two tables, Users and Requests, the latter which displays the daily
log on's of 20 users. Both tables are related by a one-to-many relationship
in the Requests table via the UserId field.

I want to run a query that when I enter a date shows all the users that DID
NOT connect 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 users that were not connected on that day. Using the
NOT LIKE ("DATE") shows all the remaining users not connected but obviously
returns all the remaining dates as well. Unfortunately, the Requests table
does not show users that are not connected as a Null field. So I cannot use
an Is Null criteria.

A copy of some sample data from the Requests table is shown below. Thus,
if "22/02/2004" is the date I want to view Users that
were not connected, based on the data below I know that UserId 3 and the
rest (which I have not shown as it would be a long list!) were not logged on
that day. So how can I display just User Id's 3 to UserId(n). I have tried
using left joins but not having any success. Of the coding that has been
recommended it tends to just return all the UserId's instead of the ones
that are left.

Hope the above is makes the problem clearer. Many thanks and I hope someone
can help!


RequestId UserId RequestDate RequestType RequestResultStatus
1 1 22/02/2004 BACKUP FAIL
2 1 22/02/2004 BACKUP FAIL
3 2 22/02/2004 BACKUP SUCCESS
4 2 27/02/2004 LIST SUCCESS
5 3 1/03/2004 BACKUP SUCCESS
6 3 1/03/2004 BACKUP SU CCESS
7 3 1/03/2004 BACKUP SUCCESS
8 3 1/03/2004 LIST SUCCESS
9 3 1/03/2004 LIST SUCCESS
 
Back
Top