Display query results if condition is true

[Guest]

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

 

[Amy Blankenship]

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

 

[Guest]

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

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

 

[Amy Blankenship]

The problem with showing a Max inside a query that returns other information
is that the max is the max from the entire recordset and doesn't necessarily
have anything to do with the rest of the fields in the record (may be from a
different record from the other fields entirely). So if it were me, I'd
make a separate query that ONLY returns the Max record for each entity,
period. Then I'd build another query on top of that that returns the rest
of the data from those records that were Max records where the inspection
results is fail.

HTH;

Amy

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

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

 

[Guest]

Hi Amy,

Thanks for the help. I’m having trouble setting up the Max query the
secondary query. I can’t get the Max query to only show me the max record for
each parcel, unless I only have the parcel id field and the Inspection Date
field in the query. Otherwise if I have any other fields it shows me all the
records for the two parcels.
I can’t really just use the parcel id field and Inspection Date because
then if I create another query and link the Inspection table to the Max Query
using the parcel id field it doesn’t know which record to use from the
Inspection table. Example below:

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
A Max query using only the Parcel Id field and Inspection Date field gives
me the following
Parcel A 2/1/06
Parcel B 2/22/06

Then I create another query using the Max query and link it to the
Inspection table (using the Parcel Id field) and add the fields from Max
query (Parcel Id (Total = Groub By) and Inspection Date (Total = Groub By))
and a couple other ones from the Inspection Table (Inspection Status – The
Criteria for this field is set to “Failâ€) it gives me the following:
Parcel A 2/1/06 Fail
Parcel B 2/22/06 Fail (this the problem. It shouldn’t have Fail but rather
Pass, which in turn means it shouldn’t be shown. Instead it’s just attaching
a Fail status to from the 11/15/05 record. Part of the problem I think is
that each of the three records associated with Parcel A and B use the same
Parcel Id number (Parcel A = A and Parcel B = B) (so the three inspection
records associated with Parcel A each have a Parcel Id of A while the three
inspection records associated with Parcel B each have a Parcel Id of B). This
was done because the Inspection table is a separate table from the Main
Septic table with the Parcel Id acting the attribute that links the records
from the two tables together.

I’ve tried using the access id number as the linking field between the Max
query and the second query but whenever I do that it just shows me all the
records again (I have the Auto Id field’s Total set to Group By). Any
thoughts or ideas as to how I might get past this. Or is there another way to
just show the closest failed inspection record for each parcel? Thanks in
advance.

MFranz

The problem with showing a Max inside a query that returns other information
is that the max is the max from the entire recordset and doesn't necessarily
have anything to do with the rest of the fields in the record (may be from a
different record from the other fields entirely). So if it were me, I'd
make a separate query that ONLY returns the Max record for each entity,
period. Then I'd build another query on top of that that returns the rest
of the data from those records that were Max records where the inspection
results is fail.

HTH;

Amy

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

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

 

[Amy Blankenship]

Hi Amy,

Thanks for the help. I'm having trouble setting up the Max query the
secondary query. I can't get the Max query to only show me the max record
for
each parcel, unless I only have the parcel id field and the Inspection
Date
field in the query. Otherwise if I have any other fields it shows me all
the
records for the two parcels.
I can't really just use the parcel id field and Inspection Date because
then if I create another query and link the Inspection table to the Max
Query
using the parcel id field it doesn't know which record to use from the
Inspection table. Example below:

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
A Max query using only the Parcel Id field and Inspection Date field gives
me the following
Parcel A 2/1/06
Parcel B 2/22/06

Then I create another query using the Max query and link it to the
Inspection table (using the Parcel Id field) and add the fields from Max
query (Parcel Id (Total = Groub By) and Inspection Date (Total = Groub
By))
and a couple other ones from the Inspection Table (Inspection Status - The
Criteria for this field is set to "Fail") it gives me the following:
Parcel A 2/1/06 Fail
Parcel B 2/22/06 Fail (this the problem. It shouldn't have Fail but rather
Pass, which in turn means it shouldn't be shown. Instead it's just
attaching
a Fail status to from the 11/15/05 record. Part of the problem I think is
that each of the three records associated with Parcel A and B use the same
Parcel Id number (Parcel A = A and Parcel B = B) (so the three inspection
records associated with Parcel A each have a Parcel Id of A while the
three
inspection records associated with Parcel B each have a Parcel Id of B).
This
was done because the Inspection table is a separate table from the Main
Septic table with the Parcel Id acting the attribute that links the
records
from the two tables together.

The only query that should use Totals is your Max query. So it should group
by ParcelID and then the date would be Max. You'd join that back to the
full table on Table.ParcelID = Query.ParcelID and Table.InspectionDate =
Query.MaxOfInspectionDate. Then put "Fail" in the criteria box under
Inspection Status.

Other than that, this second query should just be a plain jane ordinary
query where you never touched the totals button.

Unfortunately this is as much help as I can give you over a public
newsgroup. You may want to repost your question requesting that people
interested in providing paid assistance contact you privately at an e.mail
address you provide.

HTH;

Amy

 

[Guest]

Hi Amy,

I finally got it to function correctly so a big thank you for all your help
with this. I just need to do the double link in the second query and make
just a “plain jane ordinary queryâ€. After that it functioned just fine. So
thank you again, you were a great help.

MFranz

 

[Amy Blankenship]

You're welcome

Hi Amy,

I finally got it to function correctly so a big thank you for all your
help
with this. I just need to do the double link in the second query and make
just a "plain jane ordinary query". After that it functioned just fine. So
thank you again, you were a great help.

MFranz