Calculating % Change

[MEAD5432]

I have a selection query that lists the number of sales in a given month
grouped by branch. It is set up as the basis for a crosstab query that lists
the office down the side and the month across the top with the number of
sales as values.

This crosstab query is used to create a monthly sales report with six months
of trending information by branch.

I would like to calculate the % change from the previous month in the
crosstab query. For instance, the percent change in sales from October to
November.

Is this even possible to accomplish this in a crosstab query or is it
something that is better served by creating multiple selection queries and
building the formulas in by hand? I would prefer to use the crosstab query
if at all possible but am entirely open to another option if there is a
better one.

I was thinking that maybe I could use a unbound text box with an expression
to do the calculations within the report itself. Thoughts?

 

[KARL DEWEY]

Post your select and cross tab query SQL and I will show you how to produce
crosstab like this.
BRANCH Month_1 Month_2 Month_3 Month_4 Month_5 Month_6
Zone1 125 4% 234 -2% 426 6% 451 -3% 765 7% 321 -6%
Zone2 ....

 

[MEAD5432]

This is the select query:

SELECT [SALES_TABLE].BR, [SALES_TABLE].PJOFNM, Count([SALES_TABLE].ACCT) AS
CountOfACCT
FROM [SALES_TABLE]
GROUP BY [SALES_TABLE].BR, [SALES_TABLE].PJOFNM;


This is the crosstab query:

TRANSFORM Sum([By_period].[CountOfACCT]) AS SumOfCountOfACCT
SELECT [By_period].[BR]
FROM [By_period]
GROUP BY [By_period].[BR]
PIVOT [By_period].[PJOFNM] In ("OCTOBER - 07","NOVEMBER - 07","DECEMBER -
07","JANUARY - 08","FEBRUARY - 08","MARCH - 08");

Thanks for the help!

 

[KARL DEWEY]

I had to use a DateTime field instead of text that I assumed your PJOFNM
field is. I also created a table of SALES_Month to use containing the months.

SELECT SALES_TABLE.BR, SALES_TABLE.PJOFNM, Count(SALES_TABLE.ACCT) AS
CountOfACCT
FROM SALES_TABLE
GROUP BY SALES_TABLE.BR, SALES_TABLE.PJOFNM;


PARAMETERS [Begining date] DateTime, [Ending date] DateTime;
TRANSFORM Sum(IIf([Sales_Mon]=[PJOFNM],[CountOfACCT],0)) & " - " &
Sum(IIf([Sales_Mon]=[PJOFNM],[CountOfACCT],0))/Sum(IIf(DateAdd("m",-1,[Sales_Mon])=[PJOFNM],[CountOfACCT],0))*100 AS Expr1
SELECT MEAD5432.BR
FROM SALES_Month, MEAD5432
WHERE (((SALES_Month.Sales_Mon) Between [Begining date] And [Ending date]))
GROUP BY MEAD5432.BR
PIVOT SALES_Month.Sales_Mon;

--
KARL DEWEY
Build a little - Test a little

This is the select query:

SELECT [SALES_TABLE].BR, [SALES_TABLE].PJOFNM, Count([SALES_TABLE].ACCT) AS
CountOfACCT
FROM [SALES_TABLE]
GROUP BY [SALES_TABLE].BR, [SALES_TABLE].PJOFNM;


This is the crosstab query:

TRANSFORM Sum([By_period].[CountOfACCT]) AS SumOfCountOfACCT
SELECT [By_period].[BR]
FROM [By_period]
GROUP BY [By_period].[BR]
PIVOT [By_period].[PJOFNM] In ("OCTOBER - 07","NOVEMBER - 07","DECEMBER -
07","JANUARY - 08","FEBRUARY - 08","MARCH - 08");

Thanks for the help!

Post your select and cross tab query SQL and I will show you how to produce
crosstab like this.
BRANCH Month_1 Month_2 Month_3 Month_4 Month_5 Month_6
Zone1 125 4% 234 -2% 426 6% 451 -3% 765 7% 321 -6%
Zone2 ....