MS Access Cross Tab Query With Sub Total Columns

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?
Who is Participating?
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
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.
lnwrightAuthor Commented:
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?


You will; but I don't know of any other way of doing this.
lnwrightAuthor Commented:
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.


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.