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;
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;