Query for latest date for each equipment item

[Jen_RT]

I have two tables that are relevant to the query I would like to perform. The important information in each is as follows:

Equipment_TBL: with several fields but the only one of importance is the primary key - Equipment_ID

Maintenance_Record_TBL: with fields Maintenance_Record_ID (Primary key), Equipment_ID (forgein key), Overhaul_Date (date field) and other fields that are not of importance to this query.

Every time a piece of equipment undergoes maintenance, a new maintenance record is created (in the Maintenance_Record_TBL). Therefore, there can be several maintenance records for each piece of equipment.

I would like a query to find the most recent overhaul_date for each piece of equipment_ID.

I’m not sure how to do this, so any help or direction would be greatly appreciated. I also have limited SQL knowledge, but I'm prepared to learn if need be.

Cheers in advance!

 

[namliam]

Make a intermediate query to get the max maint_rec_id for each equipment id

I.e. Select Eq_ID, Max(Maint_rec_id) from Main_Rec_tbl
group by Eq_id

Save this query and use it as intermediate between your tables, this should do what you want.

 

[Jen_RT]

The problem is I am going to use this query as the basis for other test criteria so ideally I would like a single list of Maintenance records, but with only the most recent date for each equipment ID. (Sorry I should have put that in my first post)

If I make the sub query, can you still generate the single list in the format I would like?

Thanks.

 

[namliam]

doesnt change anything, if you do as I propose, you will return a single record per equipment with all your fields available to filter /search etc. on

Only problem that might arrize is, it is not editable anymore...

 

[Jen_RT]

Just incase the maintenance records have not been entered as soon as the maintenance has been conducted, or if an record was forgotten about and added at a later time, is there a way to search for the most recent date for each equipment ID.

I am trying to design the database so that it is 'idiot proof' if there is such a thing!

Cheers.

 

[namliam]

Use Max(Overhaul) instead of Max(Maint_rec_id) ?? Is that what your asking?

 

[Jen_RT]

Thanks. Thats what I thought. It worked a treat.

I shall just add the other necessary fields in another query built on this one!