Selecting most current value

[taps54]

I am working on a query to select the most current value for a given record
based on dates. The record has an associated cost for a given year say 2000.
For years 2001 to 2006 there is no cost. I want to use the cost in 2000 for
the most current cost even though my date criteria specifies say 2002. Can
this be done within the select query?

Thanks

Tony

 

[Michel Walsh]

Hi,


Definitively. For just one item:


SELECT price FROM myList WHERE dateStamp =(SELECT MAX(dateStamp) FROM myList
WHERE itemID= 9999)


as example, for the latest price of itemID 9999.


For the whole list of items, or many of them, see
http://www.mvps.org/access/queries/qry0020.htm



Hoping it may help,
Vanderghast, Access MVP