Categorising dated records into current and historical

[Christopher W via AccessMonster.com]

My database is designed to store information regarding the valuation of plant
and machinery. There is a transaction table that contains specific asset
information (model, description etc). There are several simple attached
tables for manufacturer, and client. Also there is a ‘cost’, this contains
the specific costing information about each asset. The reason for it’s
separation is due to the fact that a single asset could have multiple
valuations to update the data. This process results in each asset having
several valuations and valuation dates.

I wish to establish a query that could identify which assets require updating.
However when in the query if I put a nested if function such as IIf([Date of
Research]<[Enter Date],"Requires update"), it returns responses for all the
old already updated valuations excluding the new date indicating that the
asset has actually been updated.

How can I identify the most recent valuation for each asset as the ‘current’
valuation and then conduct a query on this?

I had thought of doing a query on a query but I still don’t know how to build
a query that will categorise the valuations into current and historical (for
each asset)!!

If someone could please help that would be most appreciated!!!!

 

[Jeff Boyce]

Christopher

"most recent" provides a clue.

Consider using a "Totals" query first, to find the most recent (Max) date.

Then build a second query that joins this first query back to the table(s)
for the details.

Good luck

Jeff Boyce
<Access MVP>