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?
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]))
0
Dale FyeCommented:
What does the SQL look like now?

Are the columns actually dates, or months?
0
Dale FyeCommented:
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
0

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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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;
0
exp vgAuthor Commented:
Awesome - the total field you suggested works. Thanks so much.
0
exp vgAuthor Commented:
Thank you very much.
0
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.
0
Dale FyeCommented:
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.
0
exp vgAuthor Commented:
Thank you.
0
Dale FyeCommented:
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.
0
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.

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.