Comparing Year to Year Periods in a report

G

Guest

I'm trying to create a report that shows item usage by month for 2003 and 2004.
ex:
Item# Item Description 2004 Qty 2003 Qty

Data is all currently in the same table in mm/dd/yyyy format.

Any ideas about how I can get create this?

Thanks in advance,

Brewer6362
 
R

Rob

There are a number of ways to do this.

One would be to use a union query to grab two sets of data
from the table and use grouping at the report level to
combine the two distinct records into a single row. If
you have a very large data set, this would probably be the
best way to go for performance reasons.

Another would be to create a query to pull the 2004
numbers, another to pull the 2003 numbers, and then create
a third query that uses these two "subqueries" as its data
sources. A potential problem here would be missing
records if one "subquery" had items the other didn't.
(You could do a left or right join to eliminate the
problem for one side or the other, but not both). If you
could guarantee the data would be consistent between both
years, this would be viable. Then again I've never seen a
data set I could totally trust, so use this with caution.

If your data set isn't huge, perhaps a better option might
be to use the QBE editor to create a single query to pull
all the records (using the date criteria of Between
#whatever2003# and #whenever2003# on one line and Between
#whatever2004# and #whenever2004# on another line to
create the OR condition) and using fields like this:

Qty2003: IIF(Year([datefield]=2003,[qty],0)

Qty2004: IIF(Year([datefield]=2004,[qty],0)

Then set this up as a Totals query and Sum these two
columns to get the year to year comparison.

HTH

Rob
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top