Update Query

[shaz123]

I have two tables one called AberdeenWOLines and AberdeenWOLinesclient, both have a field called status. I have created and update query were i am trying to update the status of AberdeenWOLinesclient to AberdeenWOLines. However when i run the update query it somtimes works correctly and sometimes does not.

For instance, within AberdeenWOLinesclient there are the following fields.

Line No WorksOrderNumber ProdNo ProdDes Qty Status
000001 0001304601 388183 Mix Cert 1 Outstanding
000001 0001305712 381210 <6c CU 1 Outstanding
000001 0001312070 503113 R22 58Kg 2 Outstanding
000002 0001304601 381183 Mix Cert 1 Outstanding
000002 0001312070 382152 <6c 6.8AL 1 Outstanding

The exact same fields and data is also within the AberdeenWOLines table. However if the some of the status's within AberdeenWOLinesclient are changed, and i click on the update query the follwing happens.

Updated AberdeenWOLinesclient table

Line No WorksOrderNumber ProdNo ProdDes Qty Status
000001 0001304601 388183 Mix Cert 1 Filled
000001 0001305712 381210 <6c CU 1 Outstanding
000001 0001312070 503113 R22 58Kg 2 Outstanding
000002 0001304601 381183 Mix Cert 1 Outstanding
000002 0001312070 382152 <6c 6.8AL 1 Outstanding

Updated AberdeenWOLines table
Line No WorksOrderNumber ProdNo ProdDes Qty Status
000001 0001304601 381183 Mix Cert 1 Filled
000001 0001305712 381210 <6c CU 1 Outstanding
000001 0001312070 503113 R22 58Kg 2 Outstanding
000002 0001304601 381183 Mix Cert 1 Filled
000002 0001312070 382152 <6c 6.8AL 1 Outstanding

As you can seeeven though only one status field from AberdeenWOLinesclient table has been changed, more then one field has been updated within AberdeenWOLines table. I have notcied that it has updated both records that have the same Worksorder number and same product, but it has not reconigsed that they are diff line numbers.

I hope i have made sense any help will be great.

 

[IGPCS]

well i geuss you linked them by one of the fields in the you got up here

you will need to link the update table by a unique index (a field that is not duplicated but match in both tables)



I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[japapuss]

You need to link the tables using primary key field, i.e. a field that contains unique values. It looks like you currently have them linked on the WorkOrderNumber field, which does not contain unique values.

 

[shaz123]

Yes ur right i have linked them with the worksorder field,thanxs for the advice i will try linking it with a primary key field