Pivot Table - Years/Month opposite Axis?

[sjd]

Is it possible to do a count of items (in this case jobs) per month, over the course of several years. (Months going horizontally, years going vertically). So that looking down the column(s), lets you quickly see if there is a trend in one month typically being slow -- or overloaded compared to the others.

I tried playing with it myself for a bit, but have not been able to get it working the way I envision it. I am hoping someone else here has run into something similar in the past.

 

[JHB]

Could you made an example in Excel, how you want it.
And post also some sample data in Excel.

 

[sjd]

Sorry for the delay, been rather busy of late.

The example is a very small sample of data, but I think it explains it well enough.

In reality, we have about 30,000 entries over the last 10 years or so. So using the actual data there should be ~ 10 rows each county being ~200-400 job numbers depending on how busy we were that month.

 

[stopher]

This looks pretty straight forward. What problem are you having? Can you post a sample database?

 

[JHB]

Remember to change the table name (JobTable) to your table name, and the 3 letters per month to fit the 3 letters of your regional settings.

TRANSFORM Count(Job) AS AntalOfJob
SELECT Year([Date Received]) AS [Year]
FROM JobTable
GROUP BY Year([Date Received])
PIVOT Format([Date Received],"mmm") In ("Jan","Feb","Mar","Apr","Maj", "Jun", "Jul","Aug","Sep", "Okt", "Nov", "Dec");

 

[sjd]

Thank you, that worked out perfectly.