Timeseriesdata query HELP!

[TTDD]

HI,
I have created a database in whivh I have stored monthly time series data. The structure of the table looks like



Date
identifier
price
sales
Item1
....


NOw I need to build a query that extracts a bunch of items for a given list of identifiers for a given date. The problem is for a few of the data items it should check if the value is present on the given date and if there is no value it should extract the value from one year before i.e ( date-12 months).
eg.IIF(ISNULL(Item1), "SELECT ITEM1 WHERE DATE=date-12M",Item1))

I ve been trying but nohting seems to work. Is it possible to do this??Thanks for the Help.

[dcrake]

You will need to create a seperate table based on the table you need to interrogate and under the date column yoiu need to apply a filter

<= DateAdd("yyyy",1,Date())

Save this query then in you other query introduce this one and create a link between the two using the known product key. Then in you price field

Price : if no price for this year use the field in the query for last year.

Price: Nz([ThisYearsPrice],[LastYearsPrice])

[TTDD]

Thanks for your help. I have created sub queries that extract data from the desired table for the desired dates. So i have a subquery for each date eg: Date+ 6 months,date +12 months,date +18 months etc and I use these in the main queries to extract the data items. Thanks again!