Solved Group by Year and Month - Crosstab Query

[jack555]

Would like to compare the monthly figures against each year. Have a totals/crosstab query grouped at month level like below.

2020-01 25
2020-02 50
2020-03 40
2021-01 20
2021-02 25
2021-03 35

However, I would like to have this in the below format

Month	2020	2021
1	25	20
2	50	25
3	40	35

Tried crosstab with year and month in column and row heading but got as per screenshot. How can we do this in a better way

 

[theDBguy]

Hi. I created the following table.

And that resulted in this crosstab query.

 

[Pat Hartman]

You have to use a numeric month to sort by otherwise your year will start with april. You can sort by the number but show the text.

 

[theDBguy]

You have to use a numeric month to sort by otherwise your year will start with april. You can sort by the number but show the text.

Oops, didn't see that. Thanks for the reminder. I guess I could try again when I get back in the office.

Cheers!

Sent from phone...

 

[CJ_London]

suspect the OP's crosstab is not correctly designed - perhaps there is another hidden grouped field as a row heading which is separating the years since the months are repeating

 

[theDBguy]

Oops, didn't see that. Thanks for the reminder. I guess I could try again when I get back in the office.

Cheers!

Sent from phone...

So, I adjusted the table like so.

And now, it comes out like this.

 

[jack555]

So, I adjusted the table like so.

View attachment 90145

And now, it comes out like this.

View attachment 90146

Thank you. It worked for me. I tested creating a totals query and generating crosstab from the total query. your sample was an eye-opener for me.

 

[jack555]

suspect the OP's crosstab is not correctly designed - perhaps there is another hidden grouped field as a row heading which is separating the years since the months are repeating

Thank you. found the problem. using "yyyy-mm" format for sorting, this was the hidden field. Thanks for the clue. Now problem solved.

 

[theDBguy]

Thank you. It worked for me. I tested creating a totals query and generating crosstab from the total query. your sample was an eye-opener for me.

Hi. Glad to hear you got it sorted out. Good luck with your project.