It didn't sound difficult when I said I could do it...

C

cookzter

I'm having a terrible time, this didn't sound difficult when I said I
would do it but now I'm NOT able to make this work. I'm working on a
database that keeps track of swimming pool inspections. In table A I
have a list of the facilities. Table B is linked to table A by ID and
contains 2 dates, 1 for when the pool operator submitted their monthly
report, the other for when the bacteria sample was received from our
lab. This is a one to many relationship because I have to keep track
of dates for each month, so there should be a record for each month in
table B. In order for the pool to remain operational it must have a
current monthly report and bacteria sample. The problem I'm having
is, I need a query that will pull out those facilities in table A that
do not have a record in table B for the previous month. I've tried
this query a million different ways but can't get what I want. I
either don't get everything or I get the records for previous months.
Does anyone have any idea how I can get the data I want?
Thanks in advance!
 
J

John Spencer

The basic way to handle this situation is to generate a query that shows all
the facilities that do have a record for the previous month. Then use that
query and the facilities table in an unmatched query (there is a wizard for
this) to identify the facilities that do not have a record.

The SQL statement for the first query
SELECT ID
FROM TableB
WHERE MonthlyReportSubmitted Between #2009-12-01# and #2009-12-31#

Now you can use that query to get those facilities that did not submit a report

The SQL statement for the second query
SELECT TableA.*
FROM TableA LEFT JOIN SavedQuery
ON TableA.ID = SavedQuery.ID
WHERE SavedQuery.ID is Null

You could also use one query solution like the one of the following
SELECT TableA.*
FROM TableA
WHERE NOT EXISTS
(SELECT *
FROM TABLEB
WHERE TableB.ID = TableA.ID AND
MonthlyReportSubmitted Between #2009-12-01# and #2009-12-31#)

SELECT TableA.*
FROM TableA
WHERE ID NOT IN
(SELECT ID
FROM TABLEB
WHERE MonthlyReportSubmitted Between #2009-12-01# and #2009-12-31#)

Of course if you need to test both whether the monthly report was submitted
and the sample was received You will have to expand the where clauses to check
for that to.

WHERE MonthlyReportSubmitted Between #2009-12-01# and #2009-12-31# AND
LabSample Between #2009-12-01# and #2009-12-31#

If you are always checking the prior month then you can make this a little
easier by using
WHERE MonthlyReportSubmitted Between
DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)
AND LabSample Between DateSerial(Year(Date()),Month(Date())-1,1) and
DateSerial(Year(Date()),Month(Date()),0)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
C

cookzter

Oh, you are a genius! It works exactly like I want it to. To think
I've spent 3 days trying to figure this out. Thank you so much for
enlightening me with your wisdom.
Theresa
 
J

John Spencer

Glad I could help. And thanks for the thanks. That is our pay.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top