Totals and Percentage Row In Crosstab Query

rhysc

New Member
Joined
Sep 19, 2005
Messages
15
Hi,

I have a crosstab query that looks much like this:

TYPE....JAN...FEB...MAR...APR...MAY...JUN...

Valid....157...465...789...154...459...489...
Inval.....15.....48.....48.....15.....46.....51....

Is it possible to have two rows underneath, one, a totals row to show total records per month and another to show percentage of totals which are valid???

Thanks
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
No. Crosstabs have only one data row. But you have 2 options:

1. If you have Access XP, look at building a pivot table (it's one of the form options). I'm pretty sure you can have more than one data field in that.

2. Build 2 crosstabs, one with the totals and one with the percentages. If the layout is the same you shoukd be able to use a Union query to combine them, and create a report from the query. In the report you can set up teh layout you're after.

There's another, Excel-based option:

3. Build a pivot table in Excel that points at the table / query in Access. You can lay that out as you want for this example. Either: (a) Data | Get External Data and build the query to return data to an Excel worksheet, then base the pivot table on that, or (b) Go Data | Pivot Table, and in the first screen opt to use an external data source. Go through the steps, and then build the pivot table as normal

Denis
 
Upvote 0
Thanks a lot.

I wasted all day yesterday messing around with crosstabs, and an access pivot table sorted me out in minutes today!
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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
Back
Top