Top 10 on Pivot Table

[shelby55]

Hello

I have a huge table in Access (800,000). My client wanted to be able to slice and dice the data the way she wants so I provided aggregrate data to her for order month, site, patient type, location, order doctor, test type, test, sum of tests.

This worked for her and she built pivot tables but when she tried to filter on top 10 doctors by sum of tests it didn't work correctly.

The pivot worksheet had page filters for: site, test, location, order month. On the pivot she had order doctor, patient type, test type.

How can I do this so the filter will show top 10 as required? Can filters be written with VBA? I know Excel has a limit for lines of data but can I import the data from Access in MSQuery (all 800,000 lines) so I can then also create a field for distinct count of patient visit and create all my summaries in MSQuery?

Thanks for any and all assistance!

 

[SkipVought]

Hi,

So you're using a PivotTable in Excel to access MS Access, using Get External Data, which is essentially MS Query within the PivotTable Source data. Yes?

Doing it that way, all your aggregations are done in the PivotTable Wizard.

Why is Top 10 not working for you?



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

The way you described is the way I WANT to do it, not the way it is now.

The current way is that I provided a summary table export to Excel from Access which my client has now added pivot tables to. My questions related to how I would do it via MSQuery i.e. can it handle importing the 800,000 records for me to summarize in MSQuery. The reason for doing so is to get a distinct count of cases (different from sum tests) since I can't get distinct count in Access.

I don't think the Top 10 is working because it isn't sorting Top 10 of grand total of tests, it seems to be assessing "top 10" on volumes per a subgroup and not the total.

For example
Doctor patienttype column group is test type
Dr Smith outpatient 5 7 8 20
Dr Smith inpatient 10 10 10 30
Dr Jones outpatient 5 5 5 15
Dr Jones inpatient 7 8 10 25

So for top 10 it should be based on grand total so Dr Smith is 50 total tests, Dr. Jones is 40.

 

[SkipVought]

Ran a text

Was able to do a TOP 10 on Sum of FIELD, which essentially selects on row grand total.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

But depending on the complexity of your report, you could do it in MS Query, even a 'crosstab' which is a TRANSFORM query.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}