473,326 Members | 2,108 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Users not connected query

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
5 1641
"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
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
"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
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Giuseppe | last post by:
Hello.. I hope someone could help me here... I've installed a phpbb forum and the mod char pjirc. I'm using IRC server irc.blitzed.org and a custom channel. Is it possibile to read via php o js...
4
by: Ryan McMinn | last post by:
Is there a utility that will allow me to see what users on my network are connected/active on SQL server? Ryan
2
by: MichaelB | last post by:
When I try logging into my web app using local host I am getting the "HTTP 403.9 - Access Forbidden: Too many users are connected Internet Information Services". The thing is I do not have 10 users...
9
by: mikharakiri_nospaum | last post by:
I have graph x y - - 1 2 2 3 3 1 4 5 and would like to query how many connected components it has (two in the example -- {1,2,3} and {4,5}). Is it doable with "recursive with"?
2
by: Sid S. | last post by:
Is there a way to programatically tell how many users are connected to IIS with c#? Or maybe some API? I don't want to restrict a certain amout of users, just restrict some features. Thanks...
0
by: Betty Harvey | last post by:
The next meeting of the XML Users Group will be held on Wednesday, June 21, 2006 at the American Geophysical Union (AGU) at 2000 Florida Avenue, N.W., Washington, DC 20009-1277. The meeting starts...
5
by: alingsjtu | last post by:
Hello, every body. When execute dynamic generated multiple OPENQUERY statements (which linkes to DB2) in SQLServer, I always got SQL1040N The maximum number of applications is already connected...
1
LMHelper
by: LMHelper | last post by:
Ok I am creating a new database for LM. One that will keep track of our licenses for over 400 users. We already have a database with the users information like name, location, telephone number,...
1
by: esource | last post by:
We have a existing backup, re-index job that runs at night on an existing database that is now accessed by web users 24/7. Problem is that one of the steps is to kill all users before backup...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.