Results 1 to 3 of 3

Totals and Percentage Row In Crosstab Query

This is a discussion on Totals and Percentage Row In Crosstab Query within the Microsoft Access forums, part of the Question Forums category; 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 ...

  1. #1
    New Member
    Join Date
    Sep 2005
    Location
    London
    Posts
    15

    Default Totals and Percentage Row In Crosstab Query

    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

  2. #2
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,213

    Default

    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
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

  3. #3
    New Member
    Join Date
    Sep 2005
    Location
    London
    Posts
    15

    Default

    Thanks a lot.

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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com