Adding in totals to a crosstab

[ryetee]

I'm having problems in adding in totals to a crosstab query (see https://access-programmers.co.uk/forums/showthread.php?t=298779)

I've googled this to death and I get solutions that say you need to union this to that and add another query etc etc. I can't fathom how to do this and how it would work as both rows and columns can vary depending upon the data.

I found 1 solution that says in the datasheet view pick total from the Records group. This adds in a total row where I can select sum/count/average for each column.

This is exactly what I want.

But it relies on the user being able to see the ribbon - it's hidden and for good reason. If I run the crosstab query and then do the above and add the totals in when I exit it asks if I want to save it which I do. However when I run it for the second time the totals aren't there unless I click the total icon and they appear in full - ie I don't have to select each field and choose sum/count/average etc

So can i do this using VBA.
(and how!)

 

[Ranman256]

when you use the query builder, it adds the totals column automatically.
did you try this?

 

[ryetee]

when you use the query builder, it adds the totals column automatically.
did you try this?

I used the query builder and have the rows totalled. I want to total the columns as well.

 

[Beetle]

Use the crosstab as the record source of a form or report and total the columns in the footer

 

[ryetee]

Use the crosstab as the record source of a form or report and total the columns in the footer

Columns and rows can be dynamic - how do I cater for that

 

[ryetee]

Just to be clear to what I'm after
The cross tab gives me
...............Row Total thing1 thing2 thing3 thing4
client 1.........2............1..........1.......0.........0
client 2......... 4............ 1.......... 0....... 3......... 0
client 3......... 4............ 1.......... 1....... 2......... 0
client 4......... 4............ 1.......... 1....... 1......... 0
client 5......... 4............ 1.......... 1....... 0......... 1
I want
...............Row Total thing1 thing2 thing3 thing4
client 1.........2............1..........1.......0.........0
client 2......... 4............ 1.......... 0....... 3......... 0
client 3......... 4............ 1.......... 1....... 2......... 0
client 4......... 4............ 1.......... 1....... 1......... 0
client 5......... 4............ 1.......... 1....... 0......... 1

...................16......... 5......... 4......... 6......... 1

As per my first post I can't see how to do this with a query. I can get this if I've got access to the ribbon but the user won't have.

 

[Beetle]

Looks like you got your solution in your other thread

 

[ryetee]

Looks like you got your solution in your other thread

Thought I had but the example doesn't look like a proper crosstab and doesn't work with mine. I'm looking to see if I can adapt but not sure I can.