adjusting a bunch of fields by one number

[ian`]

I input a variety of data in 1 table (raw data. This
table has a primary key, then the date, then a bunch of
financials (revenue data for different departments). The
problem is I need to be able to ajust the past data for
inflation to turn it into "real" currency to compare past
years.

All data is "month-end" so I'll just apply 1 inflation
number to an entire year's worth of data. But there are
about 15 departments each with 12 months worth of data.
I've created a second table for the conversion figure
(ie. 2002-2003, 2.9%: 2001-2002, 3.3%: etc...)

I could create a whole bunch of calculated fields in a
query but then I'd have to redo them all each year. Is
there an easy way to apply these inflation figures
without creating a bunch of new calculated fields?

 

[Douglas J. Steele]

What you need is an Update query. Without knowing more about your table
design, though, I can't begin to tell you what that query would look like.

 

[Guest]

As far as I can tell this will be the same amount of work
as making the individual statements. I only have 2
tables. One with the revenue data and one with the
conversion rates.

In the raw data table I've a primary key, date of month
end, revenue data and a field with the number of days
worked. I only want to change the revenue field but
there's 15 of them for each month of each year.

If created queries to seperate out the revenue data by
year so I can apply an update query to the query. But
to "correct" the revenue data I'd have to type in the
same expression for each revenue input. For instance to
convert 2001 dollars to 2004 dollars for office 1...
[revenueoffice1]*[2001conversion]*[2002conversion]*
[2003conversion] for all 15 offices then redo it for the
years 2002 and 2003.

Rather, I'm looking for a way to build an expression to
convert the data where I don't have to retype the
[revenueoffice1] expression for each of 15 offices and 4
years. Does this help? I know part of the problem is
that I've dumped all the data into a single table because
it came from an overdone excel sheet.

Help!!! (&thanks)