only the most recent record... 1

[Niko7]

I cannot find anything similar in FAQs or existing threads... please point me in the right direction if I am asking a duplicate question!

I have 2 tables. One lists truck numbers (Trucks), the other has multiple entries for each truck (Truck Activity). I need to print a report showing each truck number & it's status (latest record), like an inventory.

I wrote a query that sorts the trucks by truck number & lists the most recent record first... but how do I get only the most recent record for each truck?

I have tried some VBA code, but I feel like I am missing something easy!

 

[swampBoogie]

How do you which entry is the latest in the Activity table? Is there a timestamp or something?

Assuming that there is a timestamp and with some guessing on column/table names:

select truck.*,activity.status
from truck t inner join activity a
on t.truckPK = a.truckPk
where a.activityTime = (
select max(activityTime) from activity
where truckPk = a.truckPK )

 

[Niko7]

Thank you! Yes, I have an ID field to give each entry a unique number (timestamp).

Write this in a VBA module? I don't recognize the syntax.
What is truckPK... is that my "one-entry-per-truck" query?

Thanks again,
Niko7

 

[Niko7]

SELECT TrailerNumber.*, TrailerNumber.Number, IncomingTrailers.ID, IncomingTrailers.TrailerNo, IncomingTrailers.Number
FROM TrailerNumber, IncomingTrailers
INNER JOIN Number ON TrailerNumber.Number=IncomingTrailers.Number
WHERE (IncomingTrailers.ID)=(select max(ID) FROM ID WHERE Number = IncomingTrailers.TrailerNo);

I get a "syntax error in FROM clause"...

 

[swampBoogie]

How are your tables defined and how are they related?

What is number?

Access does not allow you to mix implicit and explicit joins so you must either use

from a,b,c
where a.id = b.id
and b.id = c.id

or

from (a inner join b on a.id = b.id) inner join c
on b.id = c.id

The parentheses must be present. Strange limitations but that's how it is.

 

[Niko7]

SwampBoogie... I have it figured it out with your help; thank you very much!

Here is my finished WORKING query in case anyone references this thread:

SELECT TrailerNumber.*, [IncomingTrailers].[ID], [IncomingTrailers].[TrailerNo]
FROM TrailerNumber, IncomingTrailers
WHERE IncomingTrailers.ID = (SELECT MAX(ID) from IncomingTrailers WHERE IncomingTrailers.TrailerNo = TrailerNumber.TrailerNo);