Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


MS Access Cross Tab Query With Sub Total Columns

Posted on 2007-10-07
Medium Priority
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
  • 2
  • 2
LVL 77

Expert Comment

ID: 20031694
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

ID: 20032124
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

ID: 20032718
You will; but I don't know of any other way of doing this.

Accepted Solution

InnovativeLogic earned 2000 total points
ID: 20033433
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

ID: 20076672
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

564 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