Query Design View - multi table query problems

[Guest]

I am working in the query design view with multiple linked tables. I want to
show all data related to a maximum inspection date for a facility. there are
many inspections done for this facility - one of the fields related to the
inspection is 'satisfactory' or 'un-satisfactory'. When I sort by
MaxInspection_Date, it gives me the Maximum date for 'satisfactory' and
'un-satisfactory' inspections.

I want ONLY the most recent inspection info... I am trying to create a
report with the most recent inspection info and whether the inspection was
satisfactory or not...

Any help would be GREATLY appreciated. I assume there is a way to program
this with SQL, but I would like to find a way to do it in the query design
view.

Thanks

 

[Ken Snell \(MVP\)]

So that we can help you, please switch the query to SQL View and copy the
SQL statement that you see there. Paste it in your reply to this post. From
that, we can assist you with modifying the query's SQL statement, which you
then can paste back into the SQL view and then switch to design grid view to
see how it looks there.

 

[Neil Sunderland]

I am working in the query design view with multiple linked tables. I want to
show all data related to a maximum inspection date for a facility. there are
many inspections done for this facility - one of the fields related to the
inspection is 'satisfactory' or 'un-satisfactory'. When I sort by
MaxInspection_Date, it gives me the Maximum date for 'satisfactory' and
'un-satisfactory' inspections.

I want ONLY the most recent inspection info... I am trying to create a
report with the most recent inspection info and whether the inspection was
satisfactory or not...

Any help would be GREATLY appreciated. I assume there is a way to program
this with SQL, but I would like to find a way to do it in the query design
view.

You could create a separate query which lists each facility with the
most recent inspection date, eg:
SELECT facility, MAX(inspection_date) AS most_recent_inspection
FROM Inspections
GROUP BY facility

Then add that query as an additional table into your existing query,
and join the [facility] and [most_recent_inspection] fields to where
they appear in your existing query.

Without details of your existing table structure, I can't say whether
you need additional fields (and therefore extra joins) in that new
query - and since you are already looking at 'multiple linked tabless'
you also run the risk of the dreaded 'Query too complex' error!