Cross Tab Query

[MattBaldry]

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.

Aug

Sep

Oct

Nov

Dec

Jan

Feb

Mar

Apr

May

Jun

Jul

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]

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.....

 

[MattBaldry]

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 By	Group By	Sum
Column Heading	Row Heading	Value

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]

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]

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]

ok. meanwhile someone else might actually post an answer for you, so keep an eye out.

 

[MattBaldry]

I have found the below.

Creating Microsoft Access Monthly Summary Crosstab Query Reports without Programming Code

Creating Microsoft Access Summary Crosstab Query Reports using a Crosstab Query and No Code showing Monthly Totals

www.fmsinc.com

I am going to give this a try later.

~Matt

 

[vba_php]

K. if it works, say so here in a new post please.

 

[namliam]

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:

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

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]

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:

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

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