Query results and display nulls?

[CR]

Hi,

I have the following tables:

1. Location
2. Server
3. Results

Table Location contains info on locations. Table Server contains info
on what servers there are, it links to location via LocationID.
Results contains info on the results of backups of servers and links
to servers via ServerID.

Now when a server backs up it post into the database Results table the
status of backup with the following fields:
ServerID, Status, Date. The date field is defaulted to Now() so it
records when the post into the database occurred in format dd/mm/yyyy
hh:mm:ss.

So I want to query all the results for a user specified date but I
also want it to list all the servers that didn't post records for that
day.

So if Location A had Servers 1,2 and 3 and location B had servers 3,4
and 6, and on the 01/01/2008 only servers 2 and 4 posted results I
would get something like:

Server Status Date
1
2 Passed 01/01/2008
3
4 Passed 01/01/2008
5
6

I don't mind have two seperate queries for this but at present I can't
get it to only use the date part of the field for a user prompted date
and for it to list those servers that have no entry for a given date.
I know that I will have to somehow deduct from the full list of
servers those that posted a result on a given date and then list whats
left but I have no idea how to do it.

Any suggestions most gratefull

 

[KARL DEWEY]

Try this --
SELECT Server, Status, Date
FROM Server LEFT JOIN [Results] ON Server.ID = [Results].ID;

 

[John Spencer]

You need a query that looks like the following.

SELECT S.Server, R.Status, R.Date
FROM Server as S LEFT JOIN
(SELECT Results.*
FROM Results
WHERE Results.Date = #1/1/2008#) as R
ON S.Server = R.Server

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[CR]

You need a query that looks like the following.

SELECT S.Server, R.Status, R.Date
FROM Server as S LEFT JOIN
(SELECT Results.*
FROM Results
WHERE Results.Date = #1/1/2008#) as R
ON S.Server = R.Server

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Hi,

Thanks for the suggestions, one step closer.

I now have the following query:

SELECT Schools.SchoolName, BackupTracking.ServerName,
SchoolServers.ServerID, BackupTracking.BackupStatus,
BackupTracking.Date
FROM Schools INNER JOIN (SchoolServers LEFT JOIN (SELECT
BackupTracking.* FROM BackupTracking WHERE BackupTracking.Date

=#30/04/2008#) BackupTracking ON SchoolServers.ServerID =

BackupTracking.ServerID) ON Schools.SchoolID = SchoolServers.SchoolID;

Which I think is display the results the way I want. The problems I'm
having are that I have to have the Where on the date as >= the date I
want, if it is just = then no results are displayed. The Date field
also includes time so guess that is the prob.
I now want it to prompt the user for the date and know that if I put
the where bit in [] it prompts but how do I get it use the one prompt
for the between dates?

Thanks for the help.

 

[John Spencer]

If the BackupTracking.Date field contains a time also (other than midnight)
then you can try using DateValue(BackupTracking.Date) or use a range

WHERE DateValue(BackupTracking.Date)=#30/04/2008#

Or use

WHERE BackupTracking.Date Between #2008-04-30# and #2008-05-01#

Access expects date literals to be in the format mm/dd/yyyy or yyyy/mm/dd. If
you use some other format, Access may well return unexpected results.
01/04/2008 will be treated as Jan 4, 2008. #30/04/2008 will be recognized as
April 30, 2008 because there is no month numbered 30, so the 30 gets treated
as the day and the 04 gets treated as the month.

SELECT Schools.SchoolName, BackupTracking.ServerName,
SchoolServers.ServerID, BackupTracking.BackupStatus,
BackupTracking.Date
FROM Schools INNER JOIN (SchoolServers LEFT JOIN
(SELECT BackupTracking.* FROM BackupTracking
WHERE DateValue(BackupTracking.Date)=#30/04/2008#)
BackupTracking ON SchoolServers.ServerID =
BackupTracking.ServerID) ON Schools.SchoolID = SchoolServers.SchoolID;

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

You need a query that looks like the following.

SELECT S.Server, R.Status, R.Date
FROM Server as S LEFT JOIN
(SELECT Results.*
FROM Results
WHERE Results.Date = #1/1/2008#) as R
ON S.Server = R.Server

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Hi,

Thanks for the suggestions, one step closer.

I now have the following query:

SELECT Schools.SchoolName, BackupTracking.ServerName,
SchoolServers.ServerID, BackupTracking.BackupStatus,
BackupTracking.Date
FROM Schools INNER JOIN (SchoolServers LEFT JOIN (SELECT
BackupTracking.* FROM BackupTracking WHERE BackupTracking.Date

=#30/04/2008#) BackupTracking ON SchoolServers.ServerID =

BackupTracking.ServerID) ON Schools.SchoolID = SchoolServers.SchoolID;

Which I think is display the results the way I want. The problems I'm
having are that I have to have the Where on the date as >= the date I
want, if it is just = then no results are displayed. The Date field
also includes time so guess that is the prob.
I now want it to prompt the user for the date and know that if I put
the where bit in [] it prompts but how do I get it use the one prompt
for the between dates?

Thanks for the help.