runsql select can I use this for my problem?

[fedum]

I have made a query from the tables Product and Sales. In this query I make the sum of the field Quantity (from table Sales, group barcode from Product, relation is set on barcode) for each Barcode in Procuct (ofcourse only the sold products). This works ok. But no I have made a new table Dimensions. I have copied all the products into this table but I would like to put the result from the query (sum from the quantity) in the field SoldQuantity (new field) in the new table. Problem is not every product has been sold so only the products in the query must be updated. Is this possible with a runsql instruction? And how?

 

[PHV]

Something like this ?
DoCmd.RunSQL "UPDATE Dimensions AS D INNER JOIN yourQuery AS Q ON D.BarCode = Q.BarCode SET D.SoldQuantity = Q.SumOfQuantity"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fedum]

I thry it out.
I have made a form from the query with a button. Do I start this runsql with this button, will he do all the records automaticly?

 

[fedum]

I get an error: Operation must use an updatable query. (Error 3073)

 

[PHV]

Another way:

DoCmd.RunSQL "UPDATE Dimensions SET SoldQuantity=DLookUp(""SumOfQuantity"",""yourQuery"",""BarCode='"" & BarCode & ""'"")"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fedum]

This likes to work but at the end I get a message about problems with conversions, key, etc... If I anwser Yes to go futher then the field SoldQuentity is not updated. I have checked the fieldtype but that seems to be ok. Barcode is Text, Soldquantity ans SumOf Quantity are Numeric. Problem?