Update Query Stops After First Record

  • Thread starter Thread starter mike
  • Start date Start date
M

mike

I'm using the update query below. This query is not
working at all, and I'm stumpted as to what is wrong.
Basically what I'm trying to accomplish these 3 things:

1.) The VeneerInventory and tblBundle tables are linked
together at each other's Flitchnum field. In the
database there is one record in VeneerInventory and then
multiple records in tblBundle for that VeneerInventory
record.

2.) The Update Query should perform the calculation
specified below for each record in tblBundle and then
place the result in the VeneerInventory's InStock field.

3.) The query should only run for records where
VeneerInventory.completd = False.


CODE:
-------------------------------------------------
UPDATE VeneerInventory INNER JOIN tblBundle ON
VeneerInventory.FlitchNum = tblBundle.Flitchnum SET
VeneerInventory.InStock = [tblbundle.BundleWidth]*
[tblbundle.BundleLength]*[tblbundle.sheetcount]/144
WHERE (((VeneerInventory.completed)=False));
 
Run a variation of the query as a Select query first. See what the results
would be. Once perfected, then morph it into the Update query. Many times
it's not doing what you think it's doing.
 
If you use square brackets, make sure the corresponding pair of square
brackets encloses only one component of the full reference to the Field. So:

[tblbundle.BundleWidth]

is incorrect. It should be:

[tblbundle].[BundleWidth]

I got a feeling that your SQL String won't work as you expected, even after
the above correction. Since you mentioned One-to-Many relationship between
VeneerInventory and tblBundle, shouldn't you sum the values of related
Records (in tblBundle) for each Record in the Table VeneerInventory?
 
Back
Top