Get last record for items

[AlexMidd]

OK, I am officially stumped and have checked the search (using above criteria produces hundreds of previous posts, none of which address this problem).

I have an Equipment table and a Certs table with a one-to-many relationship - each item is tested and certified at six monthly intervals. I need a report to show the items along with the last test details (i.e. details of the most recent test).

I have always used 'Last' in the Total line for those fields that have to come from the Certs table. The problem is this does not always produce the most recent test, especially if, by some quirk, someone creates a cert with an earlier date (shouldn't happen but it does) - the Last expression produces the last record, not the most recent test.

I was advised to use 'Max' instead, which works as far as the date ios concerned, but I also have to pull in the tester's name and any defects logged. 'Max' produces the highest value FOR EACH FIELD, which is not what I want. I want the details from those fields in the record for each item with the latest DATE.

So, if item 1 was inspected as follows:

TESTER DATE DEFECTS
Jim 1/1/02 None
Alex 1/7/02 Condemned

I get:

TESTER DATE DEFECTS
Jim 1/7/02 None

When I want:

TESTER DATE DEFECTS
Alex 1/7/02 Condemned

Of course I tried setting only the Date field to Max and the others to Group By but this then produces every cert record for each item, so I don't only get the most recent test details, I get them all duplicated as many times as there have been tests, which is not what I want.

As I have been publicly vilified here for using 'Last' and changed to 'Max' on that basis, I am very sore indeed. Can anyone set the record straight?

Have fun! )

Alex Middleton

 

[danvlas]

It's normal to get more records. That's because the Max and Last functions return the corresponding values for the group.
You need to set a condition in the query in such a way that only one record is returned, namely the one with the most recent date for a particular tester.

SELECT TESTER, DEFECTS, YourTable.DATE
FROM YourTable
WHERE (((YourTable.Date) In (SELECT Max(YourTable.Date) AS MaxDate
FROM YourTable
GROUP BY TESTER));

would normally do the trick. However, there is a flaw, because if Jim's any test date coincides with Alex's last test date, you will again have more records for Jim. I wrote the condition for the date, whereas it should have been written for the unique field (or field combination) of the query.
If you say how you uniquely identify each record, I'll post the correct condition.


"As I have been publicly vilified here for using 'Last' and changed to 'Max' on that basis, I am very sore indeed. Can anyone set the record straight?"

Come on, don't feel too frustrated. I got my lesson on this topic because of a data inconsistency in a critical system that almost left me bald. And trust me, it's not very pleasant...


Regards,

Dan