Selecting the most current values

[Guest]

I am trying to establish a table to track equipment setup parameters and
the history of changes. So far, I have a table with the following fields:
ID, EquipmentID, ValueType, Value, Date. EquipmentID is related to the ID
field in the Equipment Table, ValueType is the type of value stored in that
record, Value is the value, and Date is when it was changed.
Now I need to be able to retrieve the most current settings for each
piece of equipment and each ValueType. I can do a crosstab query with
EquipmentID as the row heading and ValueType as the column heading and
everything looks good. The problem is that when I sort by the date column
and try to only retrieve the last Value for each ValueType, the last function
doesn't work correctly. Is there a better way of doing this? I could set up
a table with fields for each ValueType and re-store all the Values even if
only one changes, but I don't think that would be very efficient.

 

[[MVP] S.Clark]

Anytime I need the most current, I do it with two queries. One to determine
the max per.whatever, then the second to match the found max to the data
record in the actual table.

 

[Guest]

Alright, here's the query that worked.... I use this as the base for my
crosstab query:

SELECT EquipmentSettings.ID, EquipmentSettings.EquipmentID,
EquipmentSettings.TypeID, EquipmentSettings.Value
FROM EquipmentSettings
WHERE
(((EquipmentSettings.Date)=DMax("[Date]","EquipmentSettings","[EquipmentID]="
& [EquipmentID] & " AND [TypeID]=" & [TypeID])));

Picks the value.... I was trying to use the built in functions in the
totals row in the query builder... just wasn't working. This seems to serve
the purpose I need. Thanks for your help.