Problem with expression (sum)

Q

Qaspec

I would like to either create a query from the following Union query or modiy
the SQL to allow me to sum the all of the key counts. When i try to create a
new query from this one and add a column with the following expression i get
error messages

Here is the expression as i try to add it in design view
Total: Sum([Week0]+[Week1]+[Week2]+[Week3])
Expression

New Query ("zQryWkCom3") attemtping to execute the expression
SELECT zQryWkCom2.Type, zQryWkCom2.KeyWord, zQryWkCom2.Week3,
zQryWkCom2.Week2, zQryWkCom2.Week1, zQryWkCom2.Week0,
Sum([Week0]+[Week1]+[Week2]+[Week3]) AS Total
FROM zQryWkCom2
GROUP BY zQryWkCom2.Type, zQryWkCom2.KeyWord, zQryWkCom2.Week3,
zQryWkCom2.Week2, zQryWkCom2.Week1, zQryWkCom2.Week0;


Here is the SQL from the original Union Query
SELECT QryDateList1.Category AS Type, QryDateList1.Type AS KeyWord,
[DateStart4] & " to " & [DateEnd4] AS Week3, [DateStart3] & " to " &
[DateEnd3] AS Week2, [DateStart2] & " to " & [DateEnd2] AS Week1,
[DateStart1] & " to " & [DateEnd1] AS Week0
FROM QryDateList1;
UNION SELECT tblRecallList.Type, tblRecallList.KeyWord, zQryWk3h.[Key Count]
AS [Week 3], zQryWk2h.[Key Count] AS [Week 2], zQryWk1h.[Key Count] AS [Week
1], zQryWk0h.[Key Count] AS [Week 0]
FROM (((tblRecallList LEFT JOIN zQryWk1h ON tblRecallList.KeyWord =
zQryWk1h.KeyWord) LEFT JOIN zQryWk0h ON tblRecallList.KeyWord =
zQryWk0h.KeyWord) LEFT JOIN zQryWk2h ON tblRecallList.KeyWord =
zQryWk2h.KeyWord) LEFT JOIN zQryWk3h ON tblRecallList.KeyWord =
zQryWk3h.KeyWord;
 
M

Michel Walsh

[DateStart3] & " to " & [DateEnd3] AS Week2

is a string. You cannot SUM strings.


Hoping it may help,
Vanderghast, Access MVP
 

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

Top