Users Not Connected Query

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
SUCCESS
7 3 1/03/2004 BACKUP
SUCCESS
8 3 1/03/2004 LIST
SUCCESS
9 3 1/03/2004 LIST
SUCCESS
 
V

Van T. Dinh

Use Left Outer Join. Your query should have an SQL String something like:

SELECT U.UserID, U.UserName
FROM tblUser As U LEFT JOIN tblRequest as R
ON U.UserID = R.UserID
WHERE (R.UserID Is Null)
AND (R.RequestDate = [Enter Request Date:])
 
B

Brian

Van T. Dinh said:
Use Left Outer Join. Your query should have an SQL String something like:

SELECT U.UserID, U.UserName
FROM tblUser As U LEFT JOIN tblRequest as R
ON U.UserID = R.UserID
WHERE (R.UserID Is Null)
AND (R.RequestDate = [Enter Request Date:])
Click to expand...

That's not going to work. R.UserId can only be null for users who have NO
records in Requests, in which case R.RequestDate will also be null, so this
query will necessarily return nothing. The date comparison needs to be part
of
the LEFT JOIN clause: see my answer in c.d.m.a.
 
V

Van T. Dinh

Thanks. I forgot the logic of Left Outer Join with this.

However, the SQL String you posted in c.d.m.a. does not work either as it
has a syntax error in the ON clause. I don't think you can put what really
is a criterion in the ON clause.

I think this will require a SubQuery, either being used in the NOT EXISTS
test or as the 2nd part of the Left Outer Join. The following tested OK in
A2002:

SELECT U.UserID, U.UserName
FROM tblUser AS U LEFT JOIN
( SELECT R.frg_UserID
FROM tblRequest AS R
WHERE R.RequestDate = [Enter Request Date]
) AS SQ
ON U.UserID = SQ.frg_UserID
WHERE ((SQ.frg_UserID) Is Null)
 
B

Brian

Van T. Dinh said:
Thanks. I forgot the logic of Left Outer Join with this.

However, the SQL String you posted in c.d.m.a. does not work either as it
has a syntax error in the ON clause. I don't think you can put what really
is a criterion in the ON clause.

I think this will require a SubQuery, either being used in the NOT EXISTS
test or as the 2nd part of the Left Outer Join. The following tested OK in
A2002:

SELECT U.UserID, U.UserName
FROM tblUser AS U LEFT JOIN
( SELECT R.frg_UserID
FROM tblRequest AS R
WHERE R.RequestDate = [Enter Request Date]
) AS SQ
ON U.UserID = SQ.frg_UserID
WHERE ((SQ.frg_UserID) Is Null)
Click to expand...

The query works fine (in A2002) exactly as I posted it in c.d.m.a. You must
have made an error of some kind. Note that the parentheses are required: it
will not work without them. Also, you can't look at a query in the query
design view when it contains this kind of join, you need to use the SQL
view, but the SQL is nonetheless valid. I use this technique in joins all
the time.

BTW, if anyone else is curious, the SQL I posted in c.d.m.a. is this:

SELECT U.UserId FROM Users U LEFT JOIN Requests R ON (U.UserId = R.UserId
AND R.RequestDate = #02/22/2004#) WHERE R.RequestId IS NULL
 
V

Van T. Dinh

Thanks, Brian.

I did work in SQL View but I left out the parentheses as you guessed.

That's why I enjoy these newgroups: always something new I can learn.
 
Top