lnwright
asked on
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?
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?
ASKER
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?
Regards,
Lee.
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?
Regards,
Lee.
You will; but I don't know of any other way of doing this.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
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.
Lee.
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.
Lee.
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.