Update Query is Missing Something

[Mike555]

I'm using the update query below. This query will run, but it does not perform the correct update. The way the join properties are currently setup, all records from both tables where the flitchnum field is equal should update. However, whenever I have several records with one flitchnum only the first record with a matching flitchnum field will update. Subsequent records with a matching flitchnum field will not update and the query moves on to update the next matching flitchnum field.


UPDATE VeneerInventory INNER JOIN tblBundle ON VeneerInventory.FlitchNum = tblBundle.Flitchnum SET VeneerInventory.InStock = [tblbundle.BundleWidth]*[tblbundle.BundleLength]*[tblbundle.sheetcount]/144
WHERE (((VeneerInventory.completed)=False));


Please let me know if I've provided adequate information. How can I fix this? Thanks.

--
Mike

 

[JonFer]

Is there a relationship set between the two tables? What are the primary keys and unique indexes on the two tables? Are they linked or internal tables?

John

http://www.interautomateconsulting.com

 

[formerTexan]

Mike,


A quick test:

Open your query in QBE (design) view, go to the menu bar (QUERY) and change the query type to SELECT.

Then run the query and see how many records are returned in datasheet view.

See if the number of records returned differs from the number you anticipate will be updated.

I suspect that your query criteria is the culprit. The number of updated records is not only determined by matching flitch numbers, but by the WHERE clause.

Cheeers,
Bill

 

[Mike555]

JonFer, These are 2 linked tables which reside on a backend dbase. Both tables have a flitchnum field (text format) which is where the tables are joined. The join type is "Only include rows where the joined fields from both tables are equal". The primary keys for both tables are autonumber fields, but the flitchnum is not the primary key in either table.

formerTexan, I've tried removing the Where clause but the same results occur. As you've suggested I've changed the query to SELECT, but the same thing occurs.

Thank you both for your help.

--
Mike

 

[JonFer]

I once had trouble when I linked tables because it asked for a unique record identifier and I chose the wrong field or combination of fields. You can verify your unique fields and re-link if that might be the problem.

I have also had problems with corrupt indexes on the backend. Rebuilding those fixed the problem.

John

http://www.interautomateconsulting.com