Comparing Amounts in prior years

[Bobbye R]

I have a query that sums all wages for all employees by year that I use in a
subreport. This is a sample of the results I'd like to have on my report.

Year Wages WageChange from prior year
% of Chg
2010 3215.83
2009 32890.51 2990.05
10%
2008 29900.46 27925.95
1414.32%
2007 1974.51 0

2010 is the current year which is in the employee header since this figure
will be changing throughout the year I don't need a wage change comparison of
the previous year. 2007 is the first year of employment and the first year
wage change is always 0. So how do I create a query to compare 2007 wage to
2008, and 2008 to 2009 etc?

 

[John W. Vinson]

I have a query that sums all wages for all employees by year that I use in a
subreport. This is a sample of the results I'd like to have on my report.

Year Wages WageChange from prior year
% of Chg
2010 3215.83
2009 32890.51 2990.05
10%
2008 29900.46 27925.95
1414.32%
2007 1974.51 0

2010 is the current year which is in the employee header since this figure
will be changing throughout the year I don't need a wage change comparison of
the previous year. 2007 is the first year of employment and the first year
wage change is always 0. So how do I create a query to compare 2007 wage to
2008, and 2008 to 2009 etc?

Please post enough of a description of your table structure that would enable
someone to answer. What's "the employee header"? Tables don't have headers, so
it's not clear what you mean...

 

[Allen Browne]

For this example, we'll assume the query you already has is named MyQuery.

1. Create another query, using two copies of MyQuery as source 'tables.'
Access will alias the second one with a "_1" suffix, i.e. MyQuery_1.

2. Drag the Year from from MyQuery, and drop onto MyQuery_1.Year. Access
will show a line joining the 2 queries on the Year field in the upper pane
of table design.

3. Double-click the join line.
Access pops up a dialog offering 3 choices.
Choose the one that says:
All records from MyQuery, and any matches from MyQuery_1.

4. Drag the Year and Wages fields from MyQuery into the output grid.

5. In a fresh column of the Field row, type:
WageChange: [MyQuery].[Wages] - [MyQuery_1].[Wages]

6. Switch the query to SQL View.
In the FROM clause, you'll see something like:
ON MyQuery.[Year] = MyQuery_1.[Year]
Edit this so it reads:
ON MyQuery.[Year] = MyQuery_1.[Year] - 1

After step 6, you will not be able to switch the query back to design view
again, but it should give you the results you need.

(Note that this query compares the year to the one that is literally the
year before, not the most recent previous year for which you happen to have
data.)