Missing semicolon error or aggregate error 1

[MAKNIGHT71]

I am adding a new SQL statement to a module in an Access database. Below is the statement I'm trying to add.

DoCmd.RunSQL "UPDATE [Total Cost Table] INNER JOIN [U Requirements] AS U ON [Total Cost Table].Item_No = U.Orig_Parent " & _
"SET [Total Cost Table]!LBR_Cost = Sum(U.Std_Cost*(U.Comp_Required_Qty*((U.Scrap_Factor/100)+1))) " & _
"WHERE Trim(U.Comp_Item_No) = 'LBR'; " ' & _
"GROUP BY [Total Cost Table]!Item_No " & _
"ORDER BY [Total Cost Table]!Item_No;", 0


As-is I get a missing semi-colon error. If I remove the GROUP BY and ORDER BY statements altogether, I get a run-time 3122 error (troublesome aggregate error). Ideas? Below is the entire code section... (the troublesome line is the third)

maknight

' Clear the table
DoCmd.RunSQL "DELETE FROM [Total Cost Table];", 0

' Fill table with BOM caculated cost w/scrap factor
DoCmd.RunSQL "INSERT INTO [Total Cost Table] " & _
"SELECT R.Parent_Item_No AS Item_No, Sum(U.Std_Cost*(U.Comp_Required_Qty*((U.Scrap_Factor/100)+1))) AS BOM_COST " & _
"FROM Requirements AS R INNER JOIN [U Requirements] AS U ON R.Parent_Item_No = U.Orig_Parent " & _
"WHERE Trim(U.Pur_or_Mfg) = 'P' OR Trim(U.Pur_or_Mfg) = 'I' OR " & _
"Trim(U.Comp_Item_No) = 'LBR' " & _
"GROUP BY R.Parent_Item_No " & _
"ORDER BY R.Parent_Item_No;", 0

' Update table with Item/Location standard cost
DoCmd.RunSQL "UPDATE [Total Cost Table] INNER JOIN IMINVLOC_SQL ON [Total Cost Table].Item_No = IMINVLOC_SQL.Item_No " & _
"Set [Total Cost Table]!Std_Cost = IMINVLOC_SQL.Std_Cost " & _
"WHERE IMINVLOC_SQL.Loc = '001';", 0

' Update table with extracted LBR cost
DoCmd.RunSQL "UPDATE [Total Cost Table] INNER JOIN [U Requirements] AS U ON [Total Cost Table].Item_No = U.Orig_Parent " & _
"SET [Total Cost Table]!LBR_Cost = Sum(U.Std_Cost*(U.Comp_Required_Qty*((U.Scrap_Factor/100)+1))) " & _
"WHERE Trim(U.Comp_Item_No) = 'LBR'; " ' & _
"GROUP BY [Total Cost Table]!Item_No " & _
"ORDER BY [Total Cost Table]!Item_No;", 0

' Update table with Item Master full item description (1 & 2)
DoCmd.RunSQL "UPDATE [Total Cost Table] INNER JOIN IMITMIDX_SQL ON [Total Cost Table].Item_No = IMITMIDX_SQL.Item_No " & _
"Set [Total Cost Table]!Description = (Trim(IMITMIDX_SQL.Item_Desc_1) & ', ' & Trim(IMITMIDX_SQL.Item_Desc_2));", 0

 

[Golom]

You have two semi-colons in the statement

"WHERE Trim(U.Comp_Item_No) = 'LBR'; " ' & _
"GROUP BY [Total Cost Table]!Item_No " & _
"ORDER BY [Total Cost Table]!Item_No;", 0

A semi-colon ends the interpretation of an SQL statement. You probably don't want the first one. It also looks like you have a spurious single quote in the first line (above).

You get the 3122 error because SQL requires that you group by the fields that do not appear in the aggregate function in order to correctly compute the aggregate.

 

[dhookom]

I wasn't aware that you could perform any updates on a group by query. The only alternative SQL would use DSum().

Duane
MS Access MVP

 

[MAKNIGHT71]

The example that is posted is after I had commented out the GROUP BY and ORDER BY clauses. The ("extra&quot semicolon has been placed after the WHERE clause, and the rest commented out (with what appears at first glance to be an extra ' mark).

I should've mentioned that.

maknight

 

[dhookom]

If you don't use Group By, you can't use Sum(). You can get by without the Group By if you use DSum().

Duane
MS Access MVP

 

[MAKNIGHT71]

Would it be better to use DSUM, or would a nested SELECT query work/work better? I'm trying to figure out my DSUM statement now to try it. There's nothing on it in any of my SQL manuals. I have always stayed away from DSUM in the past b/c I've read it's more of a cow than SUM. Thanks.

maknight

 

[dhookom]

I agree that DSum() and other domain aggregate functions are some form of farm animal. However, I think even a nested subquery might make the query not updateable.
I'm not sure on all the single and double quotes in this or the data type of Item_no but you could try:
Dim strSQL as String
StrSQL = "UPDATE [Total Cost Table] SET LBR_Cost = “ & _
“DSum(‘Std_Cost*(Comp_Required_Qty*((Scrap_Factor/100)+1)))’,” & _
“ ‘[U Requirements]’, ‘Trim(Comp_Item_No) = ‘'LBR’' and Orig_Parent =’ & [Item_No])”
DoCmd.RunSQL strSQL


Duane
MS Access MVP

 

[MAKNIGHT71]

I get a "not an updatable query error when done as follows:

DoCmd.RunSQL "UPDATE [Total Cost Table] INNER JOIN [U Requirements] ON [Total Cost Table].Item_No = [U Requirements].Orig_Parent " & _
"SET [Total Cost Table]!LBR_Cost = " & _
DSum("Std_Cost*(Comp_Required_Qty*((Scrap_Factor/100)+1))", "[U Requirements]", "Trim(Comp_Item_No) = 'LBR'&quot & _
";"

It works without the INNER JOIN, but every record gets updated with the same summed value. Ideas? I'm feeling kinda stupid right about now. Thanks for your help!!

maknight

 

[dhookom]

You didn't use the syntax that I suggested. There doesn't need to be an inner join and the "where" clause of the DSum() needs the Orig_Parent = ..."

Duane
MS Access MVP

 

[MAKNIGHT71]

Syntax error (missing operator) in query expression...

DoCmd.RunSQL "UPDATE [Total Cost Table] SET LBR_Cost = " & _
"DSum('Std_Cost*(Comp_Required_Qty*((Scrap_Factor/100)+1)))', " & _
"'[U Requirements]', 'Trim(Comp_Item_No) = 'LBR' AND Orig_Parent = [Total Cost Table].Item_No');"

I think the quotes around LBR are causing the problem.

maknight

 

[dhookom]

This still isn't my original recommendation. The ";" is not necessary. My LBR had two single quotes around it and I didn't use = [Total Cost Table].Item_No.

Did you even try my suggestion? I don't guarentee it will work the first time but it is much closer than your responses.

Duane
MS Access MVP

 

[MAKNIGHT71]

Yes, I did try it EXACTLY as you provided it. It returned nothing. The fields in the table were left empty.

maknight

 

[dhookom]

I think this is an issue of the quotes. The syntax must embed quotes within a string. You should be able to double quote to preserve a quote.
Dim strSQL as String
StrSQL = "UPDATE [Total Cost Table] SET LBR_Cost = " & _
"DSum(""Std_Cost*(Comp_Required_Qty*((Scrap_Factor/100)+1)))""," & _
"""[U Requirements]"", ""Trim(Comp_Item_No) = ""LBR"" and Orig_Parent ="" & [Item_No])"
DoCmd.RunSQL strSQL


Duane
MS Access MVP

 

[MAKNIGHT71]

Sure looks strange, doesn't it? It doesn't seem to like the double-double quotes around LBR. It throws a systax error (missing opertaor). So, I changed that to singles like a normal SQL statement would be. This runs without any errors just as the double singles did, but still without any returned sums.

Dim strSQL as String
StrSQL = "UPDATE [Total Cost Table] SET LBR_Cost = " & _
"DSum(""(Std_Cost*(Comp_Required_Qty*((Scrap_Factor/100)+1)))""," & _
"""[U Requirements]"", ""Trim(Comp_Item_No) = 'LBR' and Orig_Parent ="" & [Item_No])"
DoCmd.RunSQL strSQL

Notice the added opening parentheses on the third line before Std_Cost. I did see where that was dropped on the transition from SUM to DSUM. I feed the strSQL to a message box, and it LOOKS right. I'm trying with two Item_No's.

Here's the original INSERT INTO statement that works:

DoCmd.RunSQL "INSERT INTO [Total Cost Table] " & _
"SELECT R.Parent_Item_No AS Item_No, Sum(U.Std_Cost*(U.Comp_Required_Qty*((U.Scrap_Factor/100)+1))) AS BOM_COST " & _
"FROM Requirements AS R INNER JOIN [U Requirements] AS U ON R.Parent_Item_No = U.Orig_Parent " & _
"WHERE Trim(U.Pur_or_Mfg) = 'P' OR Trim(U.Pur_or_Mfg) = 'I' OR " & _
"Trim(U.Comp_Item_No) = 'LBR' " & _
"GROUP BY R.Parent_Item_No " & _
"ORDER BY R.Parent_Item_No;", 0

[Total Cost Table] has four fields: Item_No, STD_COST, BOM_COST, LBR_COST, and Description. STD_COST and Description are filled via two other UPDATE statements b/c their data comes from different tables. Could I integrate the LBR_COST into the initial INSERT INTO statement? Would that be simpler? The WHERE clauses would have to be different for the BOM_COST and LBR_COST, as shown.

maknight

 

[dhookom]

I recreated your tables and fields and ran a test so that I could see the error messages. The following should work:
Dim strSQL As String
strSQL = "UPDATE [Total Cost Table] SET LBR_Cost = " & _
"DSum(""Std_Cost*Comp_Required_Qty*(Scrap_Factor/100+1)""," & _
"""[U Requirements]"", ""Trim(Comp_Item_No) = 'LBR' and Orig_Parent ="" & [Item_No])"
DoCmd.RunSQL strSQL

This assumes that Item_No is numeric.

Duane
MS Access MVP

 

[MAKNIGHT71]

Item_No is alphanumeric (text).
STD_COST, LBR_COST and BOM_COST are numeric
Description is text.

[U Requirements] is a Union Query.

THANK YOU for your help with this!!

maknight

 

[dhookom]

Since Item_No is text, you may need to try:
Dim strSQL As String
strSQL = "UPDATE [Total Cost Table] SET LBR_Cost = " & _
"DSum(""Std_Cost*Comp_Required_Qty*(Scrap_Factor/100+1)""," & _
"""[U Requirements]"", ""Trim(Comp_Item_No) = 'LBR' and Orig_Parent ='"" & [Item_No] & ""'"&quot"
DoCmd.RunSQL strSQL


Duane
MS Access MVP

 

[MAKNIGHT71]

THAT DID IT!!!!

I tried that, but I didn't add one more double-quote right before the final closing paren.

THANK YOU!!!!

maknight

 

[dhookom]

Glad to finally close this thread...

Duane
MS Access MVP