Hi Amy,
Sure thing. I can get the failing records and I can get those closets to
todays date, what I'm really having trouble with is having the query only
display the failed record when it is the closest. Example below:
I have three lots - Parcel A, Parcel B and Parcel C
Parcel A has the following title v information - 12/14/05 Fail, 1/4/06
Pass, 2/1/06 Fail
Parcel B has the following title v information - 11/15/05 Fail, 1/10/06
Pass, 2/22/06 Pass
Parcel B has the following title v information - 12/1/04 Fail
What I would like the query to do is display the following Fail results:
Parcel A 2/1/06 Fail
Parcel C 12/1/04 Fail
Nothing should be displayed for Parcel B since the closest record to today's
date did not fail.
Below is the SQL language I currently have:
SELECT [Maintenance Info - Inspection Info].[PID/Maplot], Max([Maintenance
Info - Inspection Info].[Most Recent Inspection Date]) AS [MaxOfMost
Recent
Inspection Date], [Maintenance Info - Inspection Info].[Results of Most
Recent Inspection], [Main Septic Database].[Owner], [Main Septic
Database].[Street #], [Main Septic Database].[Street Name]
FROM [Main Septic Database] INNER JOIN [Maintenance Info - Inspection
Info]
ON [Main Septic Database].[PID/Maplot]=[Maintenance Info - Inspection
Info].[PID/Maplot]
WHERE ((([Maintenance Info - Inspection Info].[Most Recent Inspection
Date])<Now()))
GROUP BY [Maintenance Info - Inspection Info].[PID/Maplot], [Maintenance
Info - Inspection Info].[Results of Most Recent Inspection], [Main Septic
Database].[Owner], [Main Septic Database].[Street #], [Main Septic
Database].[Street Name]
HAVING ((([Maintenance Info - Inspection Info].[Results of Most Recent
Inspection])="Fail"));
Thanks again for all your help.
MFranz
Amy Blankenship said:
Could you clarify which you're having a problem with? Are you having
trouble
just getting failing records or are you having trouble getting the one
closest to today? And what's the actual SQL from your query? (Switch the
view of the query to sql and copy what you see there into your reply).
HTH;
Amy
Hi again everyone,
I have a table that shows and inspection date [inspdate] and and
inspection
result (pass, fail) [inspresult] for numerous properties. Each property
can
have more than one inspection record attached it it. I've set up a
query
that
currently finds a record whose inspection date is closest to today's
with
the
inspection result being fail. Looks something like this
Inspection Date (Total set to Max) | Inspection Results = "Fail" |
Inspection Date (Total set to Where) <NOW().
How can I set this up so that the query will only display the closes
record
if the inspection results = "Fail".
Thanks in advance for the help
Mfranz