compare this year to last year

[Guest]

I have 9 fast food locations in my sales table. There is a field for net
sales, month and one for year. I would like to compare January 2006 (etc)
sales to January 2007 sales in a report. Is there a way to build a query that
would compare month by month? The formula is a simple (TY-LY)/LY. All I want
to see on the report from last year is a percentage up or down and the
current months sales. I am a new user excited about reports and forms but
cannot start building those until I get this problem solved. Maybe I need 2
tables, one for 2006 and one for 2007. Help!

 

[Guest]

I'd set up 3 queries, the first gets the first year/month data, the second
gets the comparison year/month, the third query combines the two other
queries, e.g.:
Query1:
SELECT Year, Month, Sales From SalesTable WHERE Year = 2006 and Month = 1
Query2:
SELECT Year, Month, Sales From SalesTable WHERE Year = 2007 and Month = 1
Query3:
SELECT Query1.Month, Query1.Year, Query1.Sales, Query2.Year, Query2.Sales
From Query1, Query2 WHERE Query1.Month = Query2.Month ORDER BY Query2.Month

Of course you can build these queries in VB and so have the Year and Month
be varied each time.

-Dorian

 

[Guest]

Thank you. I am going to try it. Should I have my months and years in seprate
fields? I do now. Also, should I have my months formated 1,2,etc or should I
spell them out? I have them spelled out now. I was thinking that for sorting
it may make more sense to have them by number.