Adding Months of Data from a Crosstab Query

B

Bob

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
 
B

Bob

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

Crosstab Query - populating blanks with zeros 2
Crosstab Query - Populating with Zeros 4
SQL query help 6
Crosstab Query - Column Heading Dates 4
How to link Crosstab Query to other query problem... 7
Cross tab query solution 4
Crosstab query (I think) 4
Crosstab Query 2

Top