Find/Create Value with query

[RedHeadedMonster via AccessMonster.com]

I have a table that contains Parts. Another table that contains Costs for
the Parts. The costs are by year.

The Cost table has PartNumber, Year, Cost

So date would look like:

shdi-444 2008 $54.68
shdi-444 2009 $57.80
shdi-444 2010 $62.50

So....heres where I am stumped. If I want to display costs in 2009$, but if
I only have a 2008 cost I have an escalation factor to add to the 2008 Value
to get the 2009 cost. As the table is set up now, how do I run the query
that first checks to see if there is a 2009 value, and then if there is none,
find the 2008 value and escalate to create the 2009 Value.

Thanx!
RHM

 

[Dorian]

First, you can use DLookup to check for a 2009 value, then if not found use
another DLookup to check for the 2008 value.
If there is only 2008 value, you will need to run an INSERT query to add the
2009 escalated value.
A full description of DLookup is in Access Help screens.
Why not just run an update on the first of the year to create all of the
2009 values from the 2008 values?
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[John Spencer MVP]

For any one part and year the query would look like the following.

SELECT TOP 1 PartNumber
, IIF([Year]=2009,Cost,Cost + (Cost * EscalationFactor)) as CalcCost
FROM Cost
WHERE PartNumber = "shdi-444"
AND [Year] <= 2009
Order By [Year] DESC

If you need a more generic version, you could use a correlated subquery to get
the relevant value returned. Correlated sub-queries tend to be SLOW with
datasets that are more than a few thousand records.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County