Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-20-2019, 08:43 AM   #1
Razzbarry
Newly Registered User
 
Join Date: Sep 2018
Location: Aurora Colorado
Posts: 28
Thanks: 17
Thanked 0 Times in 0 Posts
Razzbarry is on a distinguished road
Crosstab query with qtr and year

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

Razzbarry is offline   Reply With Quote
Old 08-20-2019, 08:52 AM   #2
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,423
Thanks: 68
Thanked 2,707 Times in 2,592 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Crosstab query with qtr and year

your last query will do it for you, just edit the PIVOT part:



PIVOT "Qtr " & Format([Invoice Date],"q") & "-" Year([Invoice Date]);

to get it in correct year order, put the Year([Invoice Date]) first in the Pivot part of the query.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Razzbarry (08-20-2019)
Old 08-20-2019, 08:59 AM   #3
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,239
Thanks: 40
Thanked 3,646 Times in 3,516 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Crosstab query with qtr and year

the description of what you require is a bit confusing - can you provide an examples of what your required output looks like

e.g.

customerName, billtoName, Q1, Q2, Q3, Q4, 2018, 2019

Also not sure how your quarterly query is actually working - you mention the need to look at 5 or 6 quarters - your query will combine Q1 for 2018 and 2019 so you will only every have 4 quarters.

edit: arnelgp's sugestion would resolve that

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
Razzbarry (08-20-2019)
Old 08-20-2019, 08:59 AM   #4
Razzbarry
Newly Registered User
 
Join Date: Sep 2018
Location: Aurora Colorado
Posts: 28
Thanks: 17
Thanked 0 Times in 0 Posts
Razzbarry is on a distinguished road
Re: Crosstab query with qtr and year

Thank you....
I got an error and edited it to: PIVOT "Qtr " & Format([Invoice Date],"q" & "-" & "YYYY");

It works!
Razzbarry is offline   Reply With Quote
Old 08-20-2019, 09:02 AM   #5
Razzbarry
Newly Registered User
 
Join Date: Sep 2018
Location: Aurora Colorado
Posts: 28
Thanks: 17
Thanked 0 Times in 0 Posts
Razzbarry is on a distinguished road
Re: Crosstab query with qtr and year

Hi CJ,
Ultimately I would like it to look like
Supplier Name , customer name Q1 2018, Q2 2018, Q3 2018....Q2 2019. I have other metrics I will add like average runrate and other information like Gross profit.
I am definitely open to more suggestion. Thank you in advance.
Barry
Razzbarry is offline   Reply With Quote
Old 08-20-2019, 09:12 AM   #6
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,423
Thanks: 68
Thanked 2,707 Times in 2,592 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Crosstab query with qtr and year

it will be:

PIVOT "Qtr " & Format([Invoice Date],"q\-yyyy");
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-20-2019, 09:15 AM   #7
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,239
Thanks: 40
Thanked 3,646 Times in 3,516 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Crosstab query with qtr and year

Quote:
Ultimately I would like it to look like
Supplier Name , customer name Q1 2018, Q2 2018, Q3 2018....Q2 2019
then arnelgp's solution should work for you

Quote:
I have other metrics I will add like average runrate and other information like Gross profit.
have other crosstabs to determine this information then join them together linking on [PL Name]. Although better to use a PK rather than name.

If this query is to be displayed in a report, you will need to specify column names - see the query properties, but your sql will end up looking something like

Code:
TRANSFORM ...
....
....
PIVOT "Qtr " & Format([Invoice Date],"q" & "-" & "YYYY") IN ("Qtr 1 - 2018","Qtr 2 - 2018"...)
And if the report needs to be dynamic - e.g. 'last 6 quarters' will change every quarter, then you will need to be creative with the column name to indicate a specific column and have some code to modify column headers to reflect the actual quarters


__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting Month/Year within a Crosstab pickslides Queries 3 07-07-2014 05:38 PM
how to format crosstab query by month and year basilyos Queries 2 07-01-2014 03:54 AM
Crosstab Queries - Year wise query in Sequence without Skipping Years having no Data! Mohsin Malik Queries 1 09-10-2012 04:26 AM
Crosstab query - unable to format & sort pivot by year, & week with preceding zero pag9 Queries 0 07-18-2012 02:24 AM
Column Headings for Multi-Year Month-based Time Series Crosstab PaulA Queries 2 09-06-2002 04:43 AM




All times are GMT -8. The time now is 05:54 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World