Query lookup problem

  • Thread starter Thread starter WJ
  • Start date Start date
W

WJ

Can the queries do something like the following:

Table VICC: contains columns like
Code 2004Price 2003Price 2002Price 2001Price

For a given Vehicle, there is a Code, and a Year
associated. What I want to do is: for any vehicle, look up
in the VICC table, using its Code and Year, the price of
the vehicle. If a price does not exist for a given year,
then look up in the year that is closest to the Year of
that vehicle, until a price is found. This process, I
think, involves using loops. But how to do it? I'm not
sure...

Thanks for any help.

WJ
 
Hi,



Assuming you have a table

WantedYears ' table Name
year ' field name
.... ' data: one year value per record where we want a value. Then, make the
following query:


----------------------------------------------------------------------------
--
SELECT CarPrices.car,
WantedYears.year,
ref.year AS DateFromYear,
Last(CarPrices.price) AS LatestKnownPrice

FROM WantedYears,
CarPrices AS ref
INNER JOIN CarPrices ON ref.car = CarPrices.car

WHERE WantedYears.year >= CarPrices.year

GROUP BY CarPrices.car, WantedYears.year, ref.year

HAVING ref.year = Max(CarPrices.year);
----------------------------------------------------------------------------
-----





Having the above query, saved, write a crosstab (with the help of the
wizard) to get the final presentation for your data, based on that saved
query. Note that when CarPrices does not have a price, for a car, for a
year, the latest (in time) known price is used.




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top