Hi,
I am working on a forecasting tool and I need to see historical sales by supplier by customer by quarter by year.
I made two crosstab queries one for quarter and one for year but am struggling to get them together with a union. The quarter query doesn't show the year and well the year doesn't show the quarter.
Here is the sql for the year:
TRANSFORM Sum([All Sales to date].[Sales Post Split]) AS [SumOfSales Post Split]
SELECT [All Sales to date].[PL Name], [All Sales to date].[Bill To Name], Sum([All Sales to date].[Sales Post Split]) AS [Total Of Sales Post Split]
FROM [All Sales to date]
GROUP BY [All Sales to date].[PL Name], [All Sales to date].[Bill To Name]
PIVOT Format([Invoice Date],"yyyy");
and the quarter;
TRANSFORM Sum([All Sales to date].[Sales Post Split]) AS [SumOfSales Post Split]
SELECT [All Sales to date].[PL Name], [All Sales to date].[Bill To Name], Sum([All Sales to date].[Sales Post Split]) AS [Total Of Sales Post Split]
FROM [All Sales to date]
GROUP BY [All Sales to date].[PL Name], [All Sales to date].[Bill To Name]
PIVOT "Qtr " & Format([Invoice Date],"q");
Maybe I have the wrong approach altogether... Suggestions are welcome. THe allsales data table has well all the sales for the last 5 years and it is updated daily. I want to be able to access historical sales quickly typically back 5 or 6 quarters.
Thanks,
Razzbarry
p.s. Ultimately I will put this in a form for future use
I am working on a forecasting tool and I need to see historical sales by supplier by customer by quarter by year.
I made two crosstab queries one for quarter and one for year but am struggling to get them together with a union. The quarter query doesn't show the year and well the year doesn't show the quarter.
Here is the sql for the year:
TRANSFORM Sum([All Sales to date].[Sales Post Split]) AS [SumOfSales Post Split]
SELECT [All Sales to date].[PL Name], [All Sales to date].[Bill To Name], Sum([All Sales to date].[Sales Post Split]) AS [Total Of Sales Post Split]
FROM [All Sales to date]
GROUP BY [All Sales to date].[PL Name], [All Sales to date].[Bill To Name]
PIVOT Format([Invoice Date],"yyyy");
and the quarter;
TRANSFORM Sum([All Sales to date].[Sales Post Split]) AS [SumOfSales Post Split]
SELECT [All Sales to date].[PL Name], [All Sales to date].[Bill To Name], Sum([All Sales to date].[Sales Post Split]) AS [Total Of Sales Post Split]
FROM [All Sales to date]
GROUP BY [All Sales to date].[PL Name], [All Sales to date].[Bill To Name]
PIVOT "Qtr " & Format([Invoice Date],"q");
Maybe I have the wrong approach altogether... Suggestions are welcome. THe allsales data table has well all the sales for the last 5 years and it is updated daily. I want to be able to access historical sales quickly typically back 5 or 6 quarters.
Thanks,
Razzbarry
p.s. Ultimately I will put this in a form for future use