MS Access Cross Tab Query With Sub Total Columns

Posted on 2007-10-07
Last Modified: 2008-01-09
I have a list of transactions I extrac from my accounting software.   Each transaction has an account name, amount and  job number.       Each job number has a prefix that I want to use to group jobs together.  For example 1-001, 1-002, 1-003, 2-001, 2-002 etc.

I have created a cross tab query that has the account number in the first column, job numbers as the rest of the columns with the sum for each (as below).  

ACCOUNT           1-001   1-002   2-002
Sales                      500      400      300
Rent                       200       100      100          
Stationery                50         20        30

 This works fine but I also want to have a sub total columns for the each job prefix.    Eg a total for all the "1" prefix jobs, a total for all the "2" prefix jobs.    I need this to be after the individual totals for each prefix as below.

ACCOUNT           1-001   1-002   1-Total   2-002  2-Total
Sales                      500      400        900      300       300
Rent                       200       100        300      100       100
Stationery                50         20          70       30          30

How can I do this?
Question by:lnwright
    LVL 77

    Expert Comment

    It's quite laborious to do this.

    You have to build one crosstab which has the individual columns.(the 'detail' crosstab)
    Then create a second crosstab on the same data which has the same row field, but uses the summary id (prefix) as the column header. So you might have to create the 'prefix-total' column using an expression .(the 'summary' crosstab)
    You then create a new query based on your 2 crosstab queries and join them on the row fields.

    The choose the first group of columns from the detail query, and add them to the grid , and add the first prefix total from the second, summary,  crosstab to the grid.  Repeat this for each group of columns untill all columns are included.
    The result will be as you require.

    Author Comment

    Thanks peter57r,

    I forgot to say that the number of jobs and prefixes will vary.    Under this approach will I have to edit the second query each time the number of jobs/prefixes changes?


    LVL 77

    Expert Comment

    You will; but I don't know of any other way of doing this.
    LVL 1

    Accepted Solution

    I would do this by building a temporary table.
    from the original data create a maketable that builds up the relevant data
    then do an append query that adds the 1-Total, 2-Total rows (basically do a sum on the left(Account,1) value
    Then do your crosstab on the temp table

    Author Comment

    Thanks for the comments.   I managed to work it out.

    I used a union query.  The first query does all the detail amounts then the second does the totals then I union them together and do the crosstab on the result.  Apparently a crosstab sorts alphabetically so I just had to make sure my numbers/column headings were organised that way eg 100, 101, 102, "100Total" etc.

    I'll accept InnovativeLogic's answer as he is basically saying the same thing except using the append statement to build a temp table.



    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now