Adding Months of Data from a Crosstab Query

G

Guest

I am building an expression where I add the first 4 months of sales so that
it is contained in one column. However, when I run the query I receive all
the 4 months of numbers in one cell instead of in total. For example, I'll
get 4.352.10 instead of 6.45. Here is my SQL Code:
SELECT [Qry_AIT_FSE _COGS_Summary_Crosstab].Region, [Qry_AIT_FSE
_COGS_Summary_Crosstab].FSE, [Qry_AIT_FSE
_COGS_Summary_Crosstab]![2005-01]+[Qry_AIT_FSE
_COGS_Summary_Crosstab]![2005-02]+[Qry_AIT_FSE
_COGS_Summary_Crosstab]![2005-03]+[Qry_AIT_FSE
_COGS_Summary_Crosstab]![2005-04] AS Expr1
FROM [Qry_AIT_FSE _COGS_Summary_Crosstab];

Thanks
 
M

Michel Walsh

Because your crosstab seems to generate string, not number. Your result is
concatenation of these strings.

SELECT [Qry_AIT_FSE _COGS_Summary_Crosstab].Region, [Qry_AIT_FSE
_COGS_Summary_Crosstab].FSE,
eval("0" & [Qry_AIT_FSE_COGS_Summary_Crosstab].[2005-01])
+eval("0" & [Qry_AIT_FSE_COGS_Summary_Crosstab].[2005-02])
+eval("0" & [Qry_AIT_FSE_COGS_Summary_Crosstab].[2005-03]) ...




Hoping it may help,
Vanderghast, Access MVP
 
G

Guest

Thanks. This works perfectly.


--
Bob


Michel Walsh said:
Because your crosstab seems to generate string, not number. Your result is
concatenation of these strings.

SELECT [Qry_AIT_FSE _COGS_Summary_Crosstab].Region, [Qry_AIT_FSE
_COGS_Summary_Crosstab].FSE,
eval("0" & [Qry_AIT_FSE_COGS_Summary_Crosstab].[2005-01])
+eval("0" & [Qry_AIT_FSE_COGS_Summary_Crosstab].[2005-02])
+eval("0" & [Qry_AIT_FSE_COGS_Summary_Crosstab].[2005-03]) ...




Hoping it may help,
Vanderghast, Access MVP


Bob said:
I am building an expression where I add the first 4 months of sales so that
it is contained in one column. However, when I run the query I receive all
the 4 months of numbers in one cell instead of in total. For example, I'll
get 4.352.10 instead of 6.45. Here is my SQL Code:
SELECT [Qry_AIT_FSE _COGS_Summary_Crosstab].Region, [Qry_AIT_FSE
_COGS_Summary_Crosstab].FSE, [Qry_AIT_FSE
_COGS_Summary_Crosstab]![2005-01]+[Qry_AIT_FSE
_COGS_Summary_Crosstab]![2005-02]+[Qry_AIT_FSE
_COGS_Summary_Crosstab]![2005-03]+[Qry_AIT_FSE
_COGS_Summary_Crosstab]![2005-04] AS Expr1
FROM [Qry_AIT_FSE _COGS_Summary_Crosstab];

Thanks
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Adding records to filtered query via linked table 3
SQL query help 6
Data type mismatch due to ColumnHeadings in crosstab query 7
Crosstab query always showing last 3 months 1
Create a crosstab query grouped by month over 3 years 5
How do I include zero-sale months in a Crosstab query? 3
Crosstab query totals 3
Adding Calcs to a Crosstab? Or Form? 4

Top