Query to return value for a highest ID value

[scottb]

I am trying to build a query for a report that will show real estate properties and the step or phase that the property is currently in. The steps can ascend or descend depending on the course of the property sale. I have three tables: tblAssets, tblHudSteps, tblHudTransitionHistory. I would like to return the instance of a property with the correct step associated with the highest value of HUDStepTransitionHistoryID. I have tried using Max on that field but I still get all the instances of a property for the various steps it has had. I have attached the query to this post. I am not sure how to return the single instance I am looking for. Any help would be greatly appreciated....

[HansV]

The query itself doesn't have to be a Totals query. The criteria in the HUDStepTransitionHistoryID field should select the highest value for each AssetID.
One option is to set the criteria to

(SELECT Max(T.HUDStepTransitionHistoryID) FROM tblHUDStepTransitionHistory AS T WHERE T.AssetID=tblHUDStepTransitionHistory.AssetID)

and another is to use

DMax("HUDStepTransitionHistoryID","tblHUDStepTransitionHistory","AssetID=" & [tblAsset].[AssetID])

You'll find both approaches in the attached version.

Database2.zip

[scottb]

Hans,
Thank you very much. I would not have been able to come up with that on my own. I appreciate your help and time.
-Scott