Crosstab Query results/ Sum

[KrispyData]

I created a crosstab query that outputs sales by each month (month as a
column header). I am creating a second query to retrieve sums of the months
I am interested.
Let's say the sales is as follows

Month1 Month2 Month3
$200 $100 $300

Here is the formula I am using:

Last3Months: Month1 + Month2 + Month3

Instead of getting the sum which shoudl be $600,
it looks like the formula is concatenating the values resuting in 200100300

i guess i can just sum it up in Excel but wanted to see if i can do it in
Access.

 

[Jeff Boyce]

Based on what you posted, it looks like the query thinks the "values" are
"text".

Consider coercing the values into "currency" using CCurr(). That way, the
"+" should add the values.

By the way, what happens if you have a null?! Check the Nz() function...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[rebeccax]

You can use the sum feature in a crosstab query. And if memory serves, in
order to concantenate in Access, the expressions have quotation marks around
them. Like "firstname" & " " & "lastname". If you show the code you're
using we can correct it.

 

[KrispyData]

Thanks rebeccax. However, the SUM feature in the crosstab query will not
work because it sums all the sales.
for some reports, i may want only the 3 months of sales, or even a random
set of months. i want to be able to add those months sales together in
access and save my self the step of doing it in Excel.

 

[rebeccax]

Do you want your sums in the query or the report?

 

[KrispyData]

In the query if possible.

Do you want your sums in the query or the report?

 

[rebeccax]

Sorry, the syntax for concantenation is [LAST] & ", " & [FIRST].

You didn't mention if your second query is meant to be a crosstab as well.
I would assume not. If that's the case, add a column and create an
expression summing the appropriate fields and give it an alias like "total".
If you want sums at the report level, create an unbound text box and write an
expression to sum the fields you want based on their names in the report.
And if the months included in the report are controlled by the query feeding
it, the sum option should appear if you walk through the wizard to create the
report.

 

[rebeccax]

Yep! I use the Nz function to sum fields in my reports.