Developer Forum

I have created a query that examines qarterly sales for 5 regions in
the country. The query contains data for the past 6 quarters for each
region and calculates the perecentage of total sales for the quarter
fo each region.

To help enhance the report I would love to include a comparison to of
current sales to previous quarter sales and a comparison of current
sales to sales a year ago. I am having a devil of a time creating a
query to perform those actions. I am not skilled at VBA and was
hoping someone could provide me with the code that will make the
comparisons.

Below is the all of the data in my query.

Your help would be greatly appreciated!!

Quarter Region SumOfSALES SumOfSumOfSALES Percentage
2003 1Q Central 199309.65 735107.03 27.11%
2003 2Q Central 240177.63 831601.9 28.88%
2003 3Q Central 245502.85 816521.33 30.07%
2003 4Q Central 136540.24 533517.93 25.59%
2004 1Q Central 114608.1 546407.99 20.97%
2004 2Q Central 119156.12 539942.9 22.07%
2003 1Q NE 124915.62 735107.03 16.99%
2003 2Q NE 139753.83 831601.9 16.81%
2003 3Q NE 136735.48 816521.33 16.75%
2003 4Q NE 90414.14 533517.93 16.95%
2004 1Q NE 80012.9 546407.99 14.64%
2004 2Q NE 94615.41 539942.9 17.52%
2003 1Q SE 215673.22 735107.03 29.34%
2003 2Q SE 242570.81 831601.9 29.17%
2003 3Q SE 235134.88 816521.33 28.80%
2003 4Q SE 179762.23 533517.93 33.69%
2004 1Q SE 182502.66 546407.99 33.40%
2004 2Q SW 179333.25 539942.9 33.21%
2003 1Q SW 62762.27 735107.03 8.54%
2003 2Q SW 63238.55 831601.9 7.60%
2003 3Q SW 68552.6 816521.33 8.40%
2003 4Q SW 61301.1 533517.93 11.49%
2004 1Q SW 113640.5 546407.99 20.80%
2004 2Q SW 85152.37 539942.9 15.77%
2003 1Q West 132446.27 735107.03 18.02%
2003 2Q West 145861.08 831601.9 17.54%
2003 3Q West 130595.52 816521.33 15.99%
2003 4Q West 65500.22 533517.93 12.28%
2004 1Q West 55643.83 546407.99 10.18%
2004 2Q West 61685.75 539942.9 11.42%

lt;<I have created a query that examines qarterly sales for 5 regions in the
country. The query contains data for the past 6 quarters for each region
.....>>

You must have some criteria in the query that starts with today and goes
back 6 quarters from there. Since you want to add comparison to sales a year
ago, change the criteria so it only goes back 4 quarters. Then make a copy
of the query and in the copy, change the criteria so the copy starts with
the date a year ago today and goes back 4 quarters from there. Create a
union query from these two queries. The union query will then contain the
sales from the past 4 quarters as well as the four quarters previous to
that. You would create a crosstab query then for your form or report. To get
the comparison of current sales to previous quarter, do the calculation
directly on the form or report because both values will be there from the
union query. Your form or report will then look like the following assuming
3Q04 is the current quarter.:

4Q03 1Q04 2Q04
3Q04
Current Year 5MM 4.8MM 6.2MM
6.7MM(+.5MM)
Previous Year 4.3MM 5MM 4.8MM
5.8MM



"Terencetrent" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...