Pivot Table Linked to MS Access Query

rconverse

Well-known Member
Joined
Nov 29, 2007
Messages
1,187
Hello,

I've setup a pivot table using data from an Access query. I'm trying to get the data in the pivot to sort in a specific order so the pivot charts look correct. The issue is that I'm trying to sort by year and week number. I thought it would be easy but, for whatever reason, while using the pivot that's connected to the Access query, each year shows week numbers that it shouldn't. So, I started this back in September. For 2017, I should see week 35 thru 52 in my pivot table. For 2018, I should just see the one week. Instead, for each year, I see each week.

I then recreated a small version of this in Excel and created a pivot that works the way I expected.

So if my two columns are:
Code:
Week	Year
1	2018
2	2017
3	2017
4	2017

My pivot table looks like this:

Code:
Year	Week
2017	2
	3
	4
2018	1

However my pivot that sourced from the query looks like this:

Code:
Year	Week
2017    1	
        2
	3
	4
2018	1
        2
        3
        4

Why would it include all of those extra rows for either year? There is no record with a combination of week 1 and year 2017 as well as no weeks 2-3 in 2018.

Is this just some weird way pivots work when you're sourcing data from MS queries.

Thank you,
Roger
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

rconverse

Well-known Member
Joined
Nov 29, 2007
Messages
1,187
Apparently pivot tables take on this behavior if you have created a calculated item. Once I removed the calculated item, the pivot table's behavior was what I expected.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,895
Messages
5,621,493
Members
415,844
Latest member
Reda Fouad Ramzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top