Getting column totals for Crosstab (1 Viewer)

ryetee · Mar 14, 2018

I have a small system that is running on access 2010 and lower

It currently has a form that uses pivot tables.

Therefore I'm getting each row and each column with totals.

The user has now moved to access 2016 an lo and behold they've stopped using pivot tables (expletive deleted).

I thought I'd use a crosstab query but although I can see how to get the rows totalled, I can't work out how to get the columns totalled.

Any ideas how I can or whether I should be using a totally different method?

TIA.

arnelgp · Mar 14, 2018

I think you need Union your xtab to another query. But since the column if xtab query is dynamic, an element if vba will be involve to build up those columns that youll need to total.

ryetee · Mar 14, 2018

arnelgp said:
I think you need Union your xtab to another query. But since the column if xtab query is dynamic, an element if vba will be involve to build up those columns that youll need to total.

Just looked at a UNION but don't the columns have to match as well which they don't added to that the columns can be dynamic.

A S MANN · Mar 17, 2018

Add the column fields in a query and show that additional field as row in Cross tab. i am using this method successfully. eg col =1 ,col 2=3 col3=5 Total:Col1+col2+col3 . show Total in cross tab as row.

ryetee · Mar 19, 2018

A S MANN said:
Add the column fields in a query and show that additional field as row in Cross tab. i am using this method successfully. eg col =1 ,col 2=3 col3=5 Total:Col1+col2+col3 . show Total in cross tab as row.

OK I'll look at this some time. In the meantime I've given the user 2 reports swapping the rows and columns so he can see both totals. Not ideal but he seems happy enough

ryetee · Mar 21, 2018

A S MANN said:
Add the column fields in a query and show that additional field as row in Cross tab. i am using this method successfully. eg col =1 ,col 2=3 col3=5 Total:Col1+col2+col3 . show Total in cross tab as row.

OK my first thought in having 2 xtab queries is ok but user still wants grand totals for the columns and I'm not sure what you mean here.
the very nature of xtab means I don't know the number of rows or columns as they are dynamic and depend on the data.

arnelgp · Mar 21, 2018

Here is a sample of dynamic union.
View the form and close it when done.
Add more values ti the table not in xtab before and ooen the form again.

isladogs · Mar 21, 2018

Ryetee - you have 2 almost identical threads at the moment which is confusing
Please mark one solved (with a comment) & just use the other

ryetee · Mar 21, 2018

ridders said:
Ryetee - you have 2 almost identical threads at the moment which is confusing
Please mark one solved (with a comment) & just use the other

I can but one is asking how I can total the columns in a crosstab (this one) and the other says I've found out how to do it using the ribbon but that option isn't open to the user and can I achieve it in VBA. However people aren't answering the question put and coming up with solutions that should be on this thread so I'll close this.

ryetee · Mar 21, 2018

arnelgp said:
Here is a sample of dynamic union.
View the form and close it when done.
Add more values ti the table not in xtab before and ooen the form again.

By the way this works if the column headings stay the same. If I add E to the table then although this appears in the crosstab it doesn't in the Union. Or if I change A to AA it fails as well

arnelgp · Mar 21, 2018

Use the form. The union is recreated each time u open the form.

ryetee · Mar 21, 2018

arnelgp said:
Use the form. The union is recreated each time u open the form.

That's weird it didn't work first time but after closing and opening it now works.
Hmm and thanks. I'll have a further play, erm I mean test!!

ryetee · Mar 21, 2018

arnelgp said:
Use the form. The union is recreated each time u open the form.

That's brilliant! THanks a lot. Can I copy the code?

ryetee · Mar 21, 2018

arnelgp said:
Use the form. The union is recreated each time u open the form.

I spoke too soon!! What you have is a 1 dimensional crosstab. With ROW being the only 1 entry for the rows. I have 2 diemensions I have customer and products. Doesn't seem to like this.

arnelgp · Mar 21, 2018

Ok i made my data 2d.
But since yiu dont kike ut; i offer no support.

ryetee · Mar 21, 2018

arnelgp said:
Ok i made my data 2d.
But since yiu dont kike ut; i offer no support.

Not sure what yiu dont kike ut means but this looks good and if this works I'll love it.
Have to leave to go home now but will try tomorrow (or maybe later tonight)

**Edit - played around with your and it's looking good. I'll see if it works for my crosstab. THanks again.

**Edit 2 - yep works with my data as well so many many thanks. Only problem was I had to call the Total row zTotal to get it at the end. Now to get it past the user!

Getting column totals for Crosstab (1 Viewer)

ryetee

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

ryetee

Registered User.

A S MANN

New member

ryetee

Registered User.

ryetee

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Attachments

isladogs

MVP / VIP

ryetee

Registered User.

ryetee

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

ryetee

Registered User.

ryetee

Registered User.

ryetee

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Attachments

ryetee

Registered User.

Similar threads

Users who are viewing this thread