What's wrong with the query below. I have a table, which has duplicate records in it (not total duplicated but mostly). So, with those duplicate records, I want to add the values in the [CUBs Cr Amt €] field together, and put the summed value back into each value of the [CUBs Cr Amt €] field.
So, instead of having two records, with on record having a value of 10 in that field, and the other records having a value of 20 in that field, both records now show a value of 30, with other records being unaffected (i.e. if it doesn't have a duplicate and it's figure previuously was 15, it is now still 15).
Anyway tried doing that with the query below and it didn't work. Some kind of syntax error. What's the issue?
strSQL = "UPDATE [CTC Table]"
strSQL = strSQL + " SET [CTC Table].[CUBs Cr Amt €] = Temp.[CUBs Cr Amt €]"
strSQL = strSQL + " FROM [CTC Table]"
strSQL = strSQL + " INNER JOIN (SELECT SUM(CUBs Cr Amt €) FROM [CTC Table] GROUP BY [CARA Ref]) As Temp"
strSQL = strSQL + " ON [CTC Table].[CARA Ref] = Temp.[CARA Ref]"