Hi all,

I hope someone can help me with this. I have been racking my poor stupid brain for days over this. I need a way to be able to create a view of the data in a single table. Let's call it tblStatus. Here is a list of fields for the example:

StatusId (Primary Key for this table)
StatusTypeId (Foreign Key to a static list of status types)
EquipmentId (Foreign key linking to tblEquipment)
EndDate (Date that this record finishes/finished being in effect)

Now the purpose of tblStatus is to store records about the location and usage of a piece of equipment. The StatusTypeId field links into the type of usage the equipement is used for in the particular record. There is more data in the table but it does not affect the current example.

The resulting data I need is:

List of all StatusId and EquipmentId for only the LATEST (EndDate) for each EquipmentId.


In the above example data only records 8427 and 8272 would be what I wanted returned.

Can anyone point me in the right direction please?

Looks like the following code might work: