Cross Tab Query (1 Viewer)

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 18:15
Joined
Feb 5, 2019
Messages
292
Hi All,

I need to create a cross tab query for order totals by month and year. Rows will show the year and the columns show the month. The issue I have is that our financial year runs from August to July.

I am after advice to get the cross tab to display like below.
AugSepOctNovDecJanFebMarAprMayJunJul
2016-2017
2017-2018
2018-2019

I have no trouble working the cross tab in month order, but need it to display as above. Has anyone every had something similar?

~Matt
 

vba_php

Forum Troll
Local time
Today, 13:15
Joined
Oct 6, 2019
Messages
2,884
Matt,

I'm sure others here are way better at this than I am, as I don't write many crosstab queries. But if no one else has an opinion, what does your source data look like? Also, the attached zip file shows some illustrations of what cross tab queries do. I'm not sure if it can be helpful to you or not, as this was created many years ago and I haven't touched it since, but I believe it is still relevant because it worked fine back when it was done. It might be worth a look. It's possible that you can take something from it.....
 

Attachments

  • ACCESS Financial Analysis Crosstab Queries (2003).zip
    25.6 KB · Views: 500

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 18:15
Joined
Feb 5, 2019
Messages
292
Matt,

I'm sure others here are way better at this than I am, as I don't write many crosstab queries. But if no one else has an opinion, what does your source data look like? Also, the attached zip file shows some illustrations of what cross tab queries do. I'm not sure if it can be helpful to you or not, as this was created many years ago and I haven't touched it since, but I believe it is still relevant because it worked fine back when it was done. It might be worth a look. It's possible that you can take something from it.....
Thanks Adam,

I have looked at the files and they really only work based on calendar year, not financial year. The source data is pulled from Sage Accounts and I use the fields as below.
Year: Year([ORDER_DATE])Month: Month([ORDER_DATE])ITEMS_NET
SALES_ORDER
Group ByGroup BySum
Column HeadingRow HeadingValue

This gives me the correct data, but I need the months to run 8, 9, 10, 11, 12, 1, 2, 3, 4, 5, 6, 7 and the column headings then as 2016-2017 and so on.

~Matt
 

vba_php

Forum Troll
Local time
Today, 13:15
Joined
Oct 6, 2019
Messages
2,884
do you have a file that actually HAS the data in it? regardless if it is the true data or fake data? like i said, the other guys here probably know how to do this, but if they're currently asleep, I can probably help you out if I have data to work with.
 

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 18:15
Joined
Feb 5, 2019
Messages
292
do you have a file that actually HAS the data in it? regardless if it is the true data or fake data? like i said, the other guys here probably know how to do this, but if they're currently asleep, I can probably help you out if I have data to work with.
Thanks Adam,

I will draft up some fake data and fire it over this afternoon.

~Matt
 

vba_php

Forum Troll
Local time
Today, 13:15
Joined
Oct 6, 2019
Messages
2,884
ok. meanwhile someone else might actually post an answer for you, so keep an eye out.
 

namliam

The Mailman - AWF VIP
Local time
Today, 19:15
Joined
Aug 11, 2003
Messages
11,696
Trouble here is your stuck up the creek without a paddle, crosstab even the solution quoted isnt going to help you get where you want
Instead you must build your statement by hand.
First make a query like so:
Code:
Select ORDER_DATE
         , YEAR(ORDER_DATE) + IIF(month(ORDER_DATE) < 8 ; -1;0) THIS_YEAR
         , MONTH(ORDER_DATE) THIS_MONTH
         , ITEMS_NET
From SALES_ORDER
Save this query as "qryFinancialYear"

Now a new query
Code:
select THIS_YEAR
, SUM(iif(this_month=8;ITEMS_NET;0)) AUG
, SUM(iif(this_month=9;ITEMS_NET;0)) SEP
, SUM(iif(this_month=10;ITEMS_NET;0)) OCT
, etc.
from qryFinancialYear
group by THIS_YEAR

This_year you may want to "fix" into: THIS_YEAR & "-" & THIS_YEAR + 1

addendum, may be a good thing to have a table with dates to which financial year they belong etc. (Aka a date dimension)
 

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 18:15
Joined
Feb 5, 2019
Messages
292
Trouble here is your stuck up the creek without a paddle, crosstab even the solution quoted isnt going to help you get where you want
Instead you must build your statement by hand.
First make a query like so:
Code:
Select ORDER_DATE
         , YEAR(ORDER_DATE) + IIF(month(ORDER_DATE) < 8 ; -1;0) THIS_YEAR
         , MONTH(ORDER_DATE) THIS_MONTH
         , ITEMS_NET
From SALES_ORDER
Save this query as "qryFinancialYear"

Now a new query
Code:
select THIS_YEAR
, SUM(iif(this_month=8;ITEMS_NET;0)) AUG
, SUM(iif(this_month=9;ITEMS_NET;0)) SEP
, SUM(iif(this_month=10;ITEMS_NET;0)) OCT
, etc.
from qryFinancialYear
group by THIS_YEAR

This_year you may want to "fix" into: THIS_YEAR & "-" & THIS_YEAR + 1

addendum, may be a good thing to have a table with dates to which financial year they belong etc. (Aka a date dimension)

Thank you Mailman,

That worked perfect. Now displaying exactly as needed.

~Matt
 

Users who are viewing this thread

Top Bottom