Getting only the last date per record in a primary table

[Mariot]

I need to build the following query:

I have 2 tables:

tblBusiness
tblInspection

tblShops have an ID, an adress, bussines name, owner name, etc
every Shop can be inspected many times. A inspection can be futile if the store is closed. If the store is open, the inspection is made, and as result with can have “with irregularities” “without irregularities”. Two or more inspections can be made the same day (if the n – 1 time the store was close).

So, here is the basic select:

SELECT tblBusiness.idBusiness, tblBusiness.BussinessName, tblInspection.InspectionDate, tblnspeccion.InspeccionTime, tblInspection.Open, tblInspection.Irregularity
FROM tblBusiness INNER JOIN tblInspection ON tblBusiness.idBusiness = tblInspection.idBusiness;

What I need is to get the LAST inspection where the bussines was open and with irregularies. One inspection per Business, but of all business. Ah, and if there ir a newer inspection but the business was close, it doesn´t count.

I tried “Group by MAX” with InspectionDate, but it just didn´t work. For example, it showed me two inspection of the same business, not “the last” of that bussiness (it was easy to filter the Open field and the Irregularity field).

I suspect that what I need to solve this is to use two or more SQL stament, two in one., something like that. But I´m in a hurry and I just can seem to get it right.

I hope you can help me with this. Thanks for reading anyway.

P.S.: the fact that there can be more than one inspection per day can be a bitch =(

but there is a "InspectionTime" that can save that". Mmm the problem is that one inspection can be made 1 july around 20:00, but the seccond on 2 july 01:00. But we store the two inspections in 1 july... so... that mens problem.

But that issue is not so important as the previous problem up there (well, in order to solve the seccond I need to solve the firts...)

PS2: I don´t know about the title...

 

[Bilbo_Baggins_Esq]

I think what you're needing is to create one query based off the inspection table.
This should be a GROUP BY query

The idBusiness shouldbe the ONLY group by column
There should be an idInspection column in the Inspection table and that should be set to MAX
Now just add your criteria and sent them to WHERE. In your case by whatever value you use to define a successful inspection.

Then, you can join this query to the Business table off the idBusiness column, and to the Inspection table off the idInspection column via another query(s).

Is this what you're looking for?

If your Inspection table does not have an ID column, you might want to consider adding one.

 

[Mariot]

Thank you for answering

This is a duplicate of this thread http://www.access-programmers.co.uk/forums/showthread.php?p=613696 (my mistake)

Every inspection have an ID, and is linked to a Business or Store.

I tried the MAX in the query but it doesn´t do a thing =(