Users not connected query

[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

[Brian]

"Andy Davis" <ad******@westnet.com.au> wrote in message
news:41******@quokka.wn.com.au...

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

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

[Andy Davis]

Brian

Many thanks for the code which I've modified slightly as shown below to
allow the entry of any particular date. I'm currenly using MS Access 2003,
so because it's an SQL query how do I run it withiout showing the code as
currently happens when I open it in design view. Ultimately I want to
produce the results in a report, whereby a user will type in the date they
wish to see clients not connected via this query.
SELECT Users.UserId

FROM Users LEFT JOIN requests_new ON (Users.UserId = requests_new.UserId

AND requests_new.date = [ Enter Date] )

WHERE requests_new.UserId IS NULL;

Thanks again

Dawn
"Brian" <bc**@IHATESPAMclara.co.uk> wrote in message
news:10****************@sabbath.news.uk.clara.net. ..

"Andy Davis" <ad******@westnet.com.au> wrote in message
news:41******@quokka.wn.com.au...

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

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

[Brian]

"Andy Davis" <ad******@westnet.com.au> wrote in message
news:41********@quokka.wn.com.au...

Brian

Many thanks for the code which I've modified slightly as shown below to
allow the entry of any particular date. I'm currenly using MS Access 2003,
so because it's an SQL query how do I run it withiout showing the code as
currently happens when I open it in design view. Ultimately I want to
produce the results in a report, whereby a user will type in the date they
wish to see clients not connected via this query.
SELECT Users.UserId

FROM Users LEFT JOIN requests_new ON (Users.UserId = requests_new.UserId

AND requests_new.date = [ Enter Date] )

WHERE requests_new.UserId IS NULL;

Thanks again

Dawn

Hi Dawn,

If you simply create a report with this query as it's recordsource (you may
be able to do this with the wizard, depending on what you want the report to
show/look like) then when you run the report Access will prompt for the
parameter [ Enter Date ]

There a several other ways you could handle this, but this is the simplest.

Brian

[Andy Davis]

Brian

Thanks. I actually did that after I posted the message, so I was ok on that
one in the end. One last question. For the date that I enter as the search
criteria in the SQL code, is there any way I can then display that date
entered in the report. That way the user who prints the report can see the
date that clients not connected refers to. I apologise for my lack of
knowledge but I've only did a little SQL about 3 years ago and I'm afraid
I'm a little rusty, but I'm certainly enjoying getting back into it again.
Thanks in advance.

Dawn

"Brian" <bc**@IHATESPAMclara.co.uk> wrote in message
news:10****************@lotis.uk.clara.net...

"Andy Davis" <ad******@westnet.com.au> wrote in message
news:41********@quokka.wn.com.au...

Brian

Many thanks for the code which I've modified slightly as shown below to
allow the entry of any particular date. I'm currenly using MS Access
2003,
so because it's an SQL query how do I run it withiout showing the code as
currently happens when I open it in design view. Ultimately I want to
produce the results in a report, whereby a user will type in the date
they
wish to see clients not connected via this query.
SELECT Users.UserId

FROM Users LEFT JOIN requests_new ON (Users.UserId = requests_new.UserId

AND requests_new.date = [ Enter Date] )

WHERE requests_new.UserId IS NULL;

Thanks again

Dawn

Hi Dawn,

If you simply create a report with this query as it's recordsource (you
may
be able to do this with the wizard, depending on what you want the report
to
show/look like) then when you run the report Access will prompt for the
parameter [ Enter Date ]

There a several other ways you could handle this, but this is the
simplest.

Brian

[Brian]

"Andy Davis" <ad******@westnet.com.au> wrote in message
news:41******@quokka.wn.com.au...

Brian

Thanks. I actually did that after I posted the message, so I was ok on that one in the end. One last question. For the date that I enter as the search
criteria in the SQL code, is there any way I can then display that date
entered in the report. That way the user who prints the report can see the
date that clients not connected refers to. I apologise for my lack of
knowledge but I've only did a little SQL about 3 years ago and I'm afraid
I'm a little rusty, but I'm certainly enjoying getting back into it again.
Thanks in advance.

Dawn

Hi Dawn,

There's no need to apologise. If I remember rightly, your parameter was
called [ Enter Date ], or something like that. Add a text box to the report
header, and set it's control Source to:

="Report for date:" & [ Enter Date ]

Obviously you can modify the text to say whatever you like, but the
important bit is to include the name of the parameter as it is in the query.