Sum

S

Steve

I have a table that contains a list of ITEMS (end products)

I have a table that contains a list of PARTS used to build an ITEM (a bill
of material for each item)

I want to update a field in the first table with the sum of the quantity of
parts where a PARTS number is not equal to "WC*" and obviously the ITEMS
match.

So this is what I came up with:

"Update tblA inner join tblB on tblA.item = tblB.item set tblA.comp_count =
sum(tblB.quantity) where ((left(tblB.component,4) <> 'WC[R') and
(left(tblB.component,4) <> 'WC[A'));"

I get an error that the comp_count is not part of the aggregate. Am I in the
right direction or is there some better way to do this?
 
O

Ofer

try running an update query using dsum without linking the two tables
If item is number
UPDATE tblA SET tblA.comp_count = DSum("quantity","tblB","left(component,4)
<> 'WC[R' and left(component,4) <> 'WC[A' AND item =" & tblA.item);

If item is string
UPDATE tblA SET tblA.comp_count = DSum("quantity","tblB","left(component,4)
<> 'WC[R' and left(component,4) <> 'WC[A' AND item ='" & tblA.item & "'");

Update tblA inner join tblB on tblA.item = tblB.item set tblA.comp_count =
sum(tblB.quantity) where ((left(tblB.component,4) <> 'WC[R') and
 
S

Steve

thanks, will give it a try!

Ofer said:
try running an update query using dsum without linking the two tables
If item is number
UPDATE tblA SET tblA.comp_count = DSum("quantity","tblB","left(component,4)
<> 'WC[R' and left(component,4) <> 'WC[A' AND item =" & tblA.item);

If item is string
UPDATE tblA SET tblA.comp_count = DSum("quantity","tblB","left(component,4)
<> 'WC[R' and left(component,4) <> 'WC[A' AND item ='" & tblA.item & "'");
So this is what I came up with:

"Update tblA inner join tblB on tblA.item = tblB.item set tblA.comp_count =
sum(tblB.quantity) where ((left(tblB.component,4) <> 'WC[R') and
(left(tblB.component,4) <> 'WC[A'));"
Click to expand...
Click to expand...
 
Top