Ultimate & Penultimate

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am constructing a database whereby I have items and their values over the
past few years. What I am attempting to do is to have this years data (i.e.
the current years) and compare it to last years data and show the difference
between the 2 values if any. I am attempting this in Access 97 to no great
effect.

Can anyone kick me in the right direction please?

Thanking you all, as always, in anticipation

regards

PMK.
 
Here's a general answer to your general question:

Set up a query to show the fields you want for comparison; sort Descending
by your year field (which should not be name Year, since that's a reserved
word), and show the top 2 values.

For example, if your table is named tblMyTable, with fields MyYear and
MyValue, your query would be:
SELECT TOP 2 tblMyTable.MyYear , tblMyTable.MyValue
FROM tblMyTable
ORDER BY tblMyTable.MyYear DESC;

This would return two rows, with year and value for the latest two years.

To set this up in the query design grid, drag the fields you want into the
grid, set the Sort option for your year field, and in the query properties
box (accessible by right-click in the top section of the QBE grid anywhere
except on the table, or via the View menu) enter 2 in the Top Values
property.

HTH,

Rob
 
Back
Top