Hi,

I'm trying to do an update of a table off a nested select which happens to be a union... but it doesn't work.. says that there is an error in my Update statement... If I remove the UPDATE syntax, I get data returned... Any help would be great

UPDATE Churn_FormData AS a INNER JOIN
SELECT * FROM (

SELECT Initiative, CostSplit, Count(*) AS Total
FROM Table1
WHERE Format([SubmittedDt],"mmmm") = (SELECT Month FROM DtRange) AND Format([SubmittedDt],"yyyy") = (SELECT Year FROM DtRange) AND CostSplit = "Churn"
GROUP BY Initiative, CostSplit

UNION

SELECT Initiative, CostSplit, Count(*) AS Total
FROM Table2
WHERE Format([SubmittedDt],"mmmm") = (SELECT Month FROM DtRange) AND Format([SubmittedDt],"yyyy") = (SELECT Year FROM DtRange) AND CostSplit = "Churn"
GROUP BY Initiative, CostSplit

UNION

SELECT Initiative, CostSplit, Count(*) AS Total
FROM Table3
WHERE Format([SubmittedDt],"mmmm") = (SELECT Month FROM DtRange) AND Format([SubmittedDt],"yyyy") = (SELECT Year FROM DtRange) AND CostSplit = "Churn"
GROUP BY Initiative, CostSplit
) AS b

ON a.Initiative=b.Initiative SET a.January = b.Total;

EXAMPLE
UPDATE DEST INNER JOIN SOURCE ON DEST.areaid = SOURCE.areaid SET DEST.areadesc = [SOURCE].[AREADESC]
WHERE DEST.areaid)=[SOURCE].[AREAID] AND DEST.areaid)=3


You try following code with paranthesis and see whether it works or not.


UPDATE Churn_FormData AS a INNER JOIN
(SELECT * FROM (

SELECT Initiative, CostSplit, Count(*) AS Total
FROM Table1
WHERE Format([SubmittedDt],"mmmm") = (SELECT Month FROM DtRange) AND Format([SubmittedDt],"yyyy") = (SELECT Year FROM DtRange) AND CostSplit = "Churn"
GROUP BY Initiative, CostSplit

UNION

SELECT Initiative, CostSplit, Count(*) AS Total
FROM Table2
WHERE Format([SubmittedDt],"mmmm") = (SELECT Month FROM DtRange) AND Format([SubmittedDt],"yyyy") = (SELECT Year FROM DtRange) AND CostSplit = "Churn"
GROUP BY Initiative, CostSplit

UNION

SELECT Initiative, CostSplit, Count(*) AS Total
FROM Table3
WHERE Format([SubmittedDt],"mmmm") = (SELECT Month FROM DtRange) AND Format([SubmittedDt],"yyyy") = (SELECT Year FROM DtRange) AND CostSplit = "Churn"
GROUP BY Initiative, CostSplit
) AS b) as c

ON a.Initiative=c.Initiative SET a.January = c.Total;


urt

read previous EXAMPLE as following

UPDATE DEST INNER JOIN SOURCE ON DEST.areaid = SOURCE.areaid SET DEST.areadesc = [SOURCE].[AREADESC]
WHERE DEST.areaid=[SOURCE].[AREAID] AND DEST.areaid=3



urt

Trying what you suggested does not work, I get a message saying:
Operation must use an updateable query.

What I'm currently doing is a 2-prong approach by copying the data into a dummy table, then doing the update from the dummy table... but I'd like to do it in 1 step...

SELECT Initiative, Total INTO TempChurn
FROM (SELECT Initiative, CostSplit, Count(*) AS Total
FROM ProactiveBcrisProvisioning
WHERE Format([SubmittedDt],"mmmm") = (SELECT Month FROM DtRange) AND Format([SubmittedDt],"yyyy") = (SELECT Year FROM DtRange) AND CostSplit = "Churn"
GROUP BY Initiative, CostSplit

UNION

etc...

)