Querry list unsuitable record

[Anugerah]

Hi there..

I have an issue with querry
I want it to show the latest hourmeter recorded for each equipment.
But for unknwon reason it show hourmeter from other date.

For example inventory number A13001 got hourmeter 515.5 on the querry.


It should get 549.9 from date 2018/10/18 according to the equipment maintenance table

Some of them got the correct hourmeter on the querry. But other ones don't.

Please anyone advice what to do.

I have another solution. I could have made another querry that show the latest ID. Then, I would need Dlookup function to call fitted hourmeter with same ID criteria. But I keep failing to create the Dlookup function.

Thank you in advance.

i uploaded the access file on GDrive:
https://drive.google.com/file/d/1NJx...ew?usp=sharing

[Anugerah]

Here is the design of the querry

[davegri]

Try it this way. Not sure you want the ID there at all. The ID is not always reliably going to be the "Latest Record".

[Anugerah]

Try it this way. Not sure you want the ID there at all. The ID is not always reliably going to be the "Latest Record".

Thanks Dave

But I did not set the hourmeter to max because there is certain case the hourmeter is reset to 0.

So I don't need it to show the largest amount of hourmeter.
I need it to show the latest hourmeter recorded for every equipment instead.

[davegri]

The problem appears to be with LAST.
I don't know why it wants to select the record with Hourmeter reading 515.5 when the record with Hourmeter reading 549.9 has a higher ID, so was created later.
To illustrate:

SELECT Last([Equipment Maintenance].Hourmeter) AS LastOfHourmeter, [Equipment Maintenance].Equipment, [Equipment Maintenance].ID
FROM [Equipment Maintenance]
GROUP BY [Equipment Maintenance].Equipment, [Equipment Maintenance].ID
HAVING Last([Equipment Maintenance].Hourmeter)>525 AND [Equipment Maintenance].Equipment=21;