Query Select previous month volume to calculate sales

[kastaman]

Hi there,

I currrently have a crosstab query where the monthy volumes are exctracted and I have a formula to calculate the total commission by product YTD.

Is there a way to show on another column just the previous month's commission?

Grand Total 2003 / 11 2003 / 12 2004 / 01
$17.64 77.00
$1.02 10.00 21.00 15.00
$4,374.48 44,941.00 42,272.00 47,051.00
$11,559.43 17,272.00 9,330.00 3,613.00
$2,719.08 20,750.00 19,511.00 38,600.00
$183,782.11 201,766.00 240,729.00 226,430.00
$4,840.49 150.00 154.00 25,810.00

My SQL is:
TRANSFORM Sum([ImpressionPrices Query].[Total Impressions]) AS [SumOfTotal Impressions]
SELECT [ImpressionPrices Query].Client, [ImpressionPrices Query].Form, [ImpressionPrices Query].Price, Sum([ImpressionPrices Query].SubTotal) AS SubTotal, Sum([ImpressionPrices Query].GST) AS GST, Sum([ImpressionPrices Query].PST) AS PST, Sum([ImpressionPrices Query].[Grand Total]) AS [Grand Total], Sum([ImpressionPrices Query].[Total Impressions]) AS [Total Impressions]
FROM [ImpressionPrices Query]
GROUP BY [ImpressionPrices Query].Client, [ImpressionPrices Query].Form, [ImpressionPrices Query].Price
ORDER BY [ImpressionPrices Query].[Billing Month] DESC
PIVOT [ImpressionPrices Query].[Billing Month];

Thanks in advance,

Kastaman

 

[JonFer]

Can you post the SQL for the underlying query as well? It looks like the commission is calculated there or earlier.

John

http://www.interautomateconsulting.com

 

[kastaman]

JonFer,

Here is the underlying query:
SELECT [tbl_Client_Volumes].[Client], [tbl_Client_Volumes].[Form], Format$([tbl_Client_Volumes].[Date],'yyyy / mm') AS [Billing Month], [ImpressionPrices].[Price], Sum([tbl_Client_Volumes].[Impressions]) AS [Total Impressions], [tbl_Fiscal_Year].[Fiscal Year], ([Total Impressions]*[Price]) AS SubTotal, ([SubTotal]*0.07) AS GST, ([SubTotal]*0.08) AS PST, ([SubTotal]+[GST]+[PST]) AS [Grand Total]
FROM tbl_Fiscal_Year INNER JOIN (ImpressionPrices INNER JOIN tbl_Client_Volumes ON [ImpressionPrices].[Form]=[tbl_Client_Volumes].[Form]) ON [tbl_Fiscal_Year].[Billing Month]=[tbl_Client_Volumes].[Date]
GROUP BY [tbl_Client_Volumes].[Client], [tbl_Client_Volumes].[Form], Format$([tbl_Client_Volumes].[Date],'yyyy / mm'), [ImpressionPrices].[Price], [tbl_Fiscal_Year].[Fiscal Year]
HAVING (((tbl_Fiscal_Year.[Fiscal Year])="2004"))
ORDER BY [tbl_Client_Volumes].[Client], Format$([tbl_Client_Volumes].[Date],'yyyy / mm');

There is a change in resulting information, we need to have the commission on a monthly basis by client.


Thanks in advance,

Kastaman