Access - Crosstab - Sum on the Value Count Field

I have a crosstab query, where the rows are colors, the columns are dates, and the values are the counts the colors appear by month. I would like to create one last column where I sum on the value counts of colors.

The formula Total:Sum(Count of Date) does not work.

Please advise if this is possible.

Thank you.
exp vgAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

exp vgAuthor Commented:
The following formula does not work either:

Total: Sum(Count([Color]))
Dale FyeOwner, Developing Solutions LLCCommented:
What does the SQL look like now?

Are the columns actually dates, or months?
Dale FyeOwner, Developing Solutions LLCCommented:
My guess is that you can add a computed column:

Field:     Total: [DateField]
Table:   YourTableName
Total:    Count
Crosstab: RowHeading

When you run the query, it will probably put this new "Total" column next to your "Color" column, but you can drag that column to the right of the query and save the query.  The next time you run the query, it should show up on the right, unless the next query has more "months" columns, in which case the new months will show up to the right.
example

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

exp vgAuthor Commented:
Please find the SQL below:

TRANSFORM Count([Table].Color) AS CountOfColor
SELECT [Table].Color
FROM [Table]
GROUP BY [Table].Color
PIVOT [Table].DateYYYYMM;
exp vgAuthor Commented:
Awesome - the total field you suggested works. Thanks so much.
exp vgAuthor Commented:
Thank you very much.
exp vgAuthor Commented:
Just one more point - the Totals column ends up as the first column. Is there a way I can make it the last. Additionally - I am unable to sort on this - and I receive a message that this is not possible.

Please confirm.

Thank you.
Dale FyeOwner, Developing Solutions LLCCommented:
The query will normally put the Total column as the right most column, prior to your monthly columns which are the actual pivoted data.  I generally don't display data in this query format.  I normally create a form or report to display this information.  The down side of this is that when you base a form or report off of a crosstab query, you generally have to write some code to hide/unhide columns and change the textbox control source and caption in order to display the information properly.

You might want to look at my accepted solution to another similar thread or here.
exp vgAuthor Commented:
Thank you.
Dale FyeOwner, Developing Solutions LLCCommented:
Glad I could help.

Crosstab queries can be very powerful at providing data for the user to view in a form or report.  But to do so, you must find a technique you are comfortable with regarding configuring form and report controls.

Good luck.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.