Aggregate Queries

[KornGeek]

I have a table of items, and a table of transactions on these items. I am trying to find the latest value of a field that is modified in these transactions.

I started by creating a query that selects transactions of a valid type. I then use a second query to find the most recent of these transactions. A third query pulls the field from the most recent transaction.

The problem I'm having is with the second query.
My first version looked like this:

SELECT qryUpdates.ItemID, Max(qryUpdates.TransDateTime) AS MaxOfTransDateTime
FROM qryUpdates
GROUP BY qryUpdates.ItemID;

My second version looked like this:

SELECT tblItems.ItemID, DMax("TransDateTime","qryUpdates","ItemID = '" & [tblItems].[ItemID] & "'") AS MaxOfTransDateTime
FROM tblItems;

The problem with the first query is that it then makes other queries that display this field non-updatable. The problem with the second query is that it is slow.

Can anybody offer suggestions of how I might better do this?

Thank you.

 

[ByteMyzer]

What is the layout of the actual table from which the query(ies) are derived?
ex. Table name: tblUpdates
[tt]
ItemID | TransDateTime | Value
-------------------------------------
000001 | 7/25/2004 3:31:54 PM | 1234
000001 | 7/25/2004 4:37:24 PM | 5678
000002 | 7/25/2004 4:37:24 PM | 9012
[/tt]

For a table such as the above example, to obtain the most recent value for each ItemID, you would use the following query:
[tt]
SELECT t1.ItemID, t1.TransDateTime, t1.Value
FROM tblUpdates AS t1
WHERE t1.TransDateTime = (
SELECT Max(t2.TransDateTime)
FROM tblUpdates AS t2
WHERE t2.ItemID=t1.ItemID;
);
[/tt]