By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,682 Members | 1,980 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,682 IT Pros & Developers. It's quick & easy.

Users not connected query

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
"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
Nov 13 '05 #2

P: n/a
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

Nov 13 '05 #3

P: n/a
"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
Nov 13 '05 #4

P: n/a
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

Nov 13 '05 #5

P: n/a
"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.
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.