Query latest record for multiple criteria (1 Viewer)

[Steve@trop]

I have a table that contains readings from several pieces of equipment as well as the status of each one. Each record has a timestamp, equipment number, status, etc. What I want is to create a query that will return the latest record for each equipment number. Simplified example table:

Timestamp EquipmentNumber Status
Today ------Machine1 ----------Running
Today ------Machine2 ----------Running
Yesterday -Machine1 ----------Down
Yesterday -Machine2 ----------Running

There are more than 20 different Equipment numbers and they are read several times per day and sometimes some of them get missed. What I'm looking for is a way to get a list of all the machines with their latest reading so they can tell which machines are running and which are down based on the last time they were read (instead of specifying a date). I can get this for one machine with no problem. I'm having trouble getting it for more than one machine. I tried a union query (with just 2 of the machines included for testing) but it only returns the results from one machine:

SELECT TOP 1 TestCompressorRoundQuery.LoggedAt,  TestCompressorRoundQuery.AssetNumber,  
TestCompressorRoundQuery.CompressorID, TestCompressorRoundQuery.Status, TestCompressorRoundQuery.CompressorIntegrity, TestCompressorRoundQuery.Notes
FROM TestCompressorRoundQuery
WHERE (((TestCompressorRoundQuery.AssetNumber)="104399"))
UNION ALL
SELECT TOP 1 TestCompressorRoundQuery.LoggedAt,  TestCompressorRoundQuery.AssetNumber, 
 TestCompressorRoundQuery.CompressorID, TestCompressorRoundQuery.Status, TestCompressorRoundQuery.CompressorIntegrity, TestCompressorRoundQuery.Notes
FROM TestCompressorRoundQuery
WHERE (((TestCompressorRoundQuery.AssetNumber)="206"))

Does anybody have a query that will do this? I'd rather not have to create a seperate query for each machine and then join all of those together! I think perhaps a Union query might not be the correct approach. All the data is coming from only one table.

Thanks,

Steve B.

 

[willknapp]

You ought to be able to use an aggregate query to find what you need.

http://office.microsoft.com/en-us/access-help/count-data-by-using-a-query-HA010096311.aspx

Scroll down for instructions for using Maximum and Minimums, which when applied to date fields, will give you the Newest or Oldest values, respectively.

 

[Steve@trop]

Thanks for the quick reply. What I need is a way to get MULTIPLE maximums (one for each machine number) from the same table of data. I need it to return the newest record for Machine 1 AND the newest record for Machine 2 and so on.

 

[willknapp]

The SQL below uses aggregate functions to show the latest status for each machine. You want to Group By Equipment Number, showing the Max TimeStamp for that group along with the First Status Value.

SELECT tblEquipmentStatus.EquipmentNumber, 
       Max(tblEquipmentStatus.Timestamp) AS MaxOfTimestamp, 
       First(tblEquipmentStatus.Status) AS FirstOfStatus
FROM tblEquipmentStatus
GROUP BY tblEquipmentStatus.EquipmentNumber
ORDER BY Max(tblEquipmentStatus.Timestamp) DESC;

I can't post a screenshot, unfortunately, but I can walk you through the steps:

1. Add the EquipmentNumber, Timestamp and Status fields to the query.
2. Click the "Σ Totals" button.
3. Change the value in the "Total:" row as follows:
-EquipmentNumber: "Group By"
-Timestamp: "Max"
-Status: "First"
4. Sort by descending Timestamp.

That should give you the latest status value for each machine, as well as the time that status was updated.

 

[Steve@trop]

That's exactly what I needed! I've always had trouble with the totals thing in Access. I should be able to customize this from here though.

Thanks a bunch!

 

[Steve@trop]

Well, I thought it worked until I looked at the results a little more closely. The machine names are correct, the dates are the latest dates but for some reason the CompressorIntegrity field is incorrect. I've never seen this before because the actual record in the table doesn't correspond with what the query returned. Two fields are correct, the third field is not. Here is the query SQL code:

SELECT Max(NorthCompressorRoundT.LoggedAt) AS [Time], NorthCompressorRoundT.CompressorID AS ID, First(NorthCompressorRoundT.CompressorIntegrity) AS FirstOfCompressorIntegrity
FROM NorthCompressorRoundT
GROUP BY NorthCompressorRoundT.CompressorID
ORDER BY Max(NorthCompressorRoundT.LoggedAt) DESC;

Any ideas why this is working so wierdly? I just realized that I didn't have the ORDER BY in there but it does the same thing even with it there.

 

[Steve@trop]

I didn't figure out why the original solution doesn't work but I did come up with a way that does work. Now I have 2 queries. The first query (testCompressorRoundQ) finds the latest date/time for each equipment number:

SELECT NorthCompressorRoundT.CompressorID AS ID, Max(NorthCompressorRoundT.LoggedAt) AS [Time]
FROM NorthCompressorRoundT
GROUP BY NorthCompressorRoundT.CompressorID;

The second query (Test2CompressorRoundQ) uses the first query to find the records I'm intersted in:

SELECT NorthCompressorRoundT.LoggedAt AS [Time], NorthCompressorRoundT.CompressorID AS ID, NorthCompressorRoundT.CompressorIntegrity AS Integrity, NorthCompressorRoundT.Status, NorthCompressorRoundT.Notes AS Comments
FROM TestCompressorRoundQ INNER JOIN NorthCompressorRoundT ON (TestCompressorRoundQ.ID = NorthCompressorRoundT.CompressorID) AND (TestCompressorRoundQ.Time = NorthCompressorRoundT.LoggedAt)
WHERE (((NorthCompressorRoundT.LoggedAt)=[TestCompressorRoundQ]![Time]));

So far this looks like it does the trick. Nice thing about it is I can add as many fields to the second query as I want without having to specify an aggregate function (Group by, First, etc) and I can add criteria to further filter results.

Thanks for all your help!

Steve