Update Query/Append Query

[shaz123]

Hi All,

I am using an update query however when clicking on the run button within the query, i keep reciving an error message which i have attached. It basically refering that due to setting a primary Key on the table i cannot add all the records, however i need to set a primary key to the table to stop duplicate entries from being updated. Any help im confused.


The table is set up as:
Line Number (Primary Key)
WorkOrderNumber (Primary Key)
ProdNo (Primary Key)
ProductDescription
Quantity
CylinderSerial Number
Status


I have set the first three fields as primary keys as there cannot be a Workorder number with the same line number and product number as another

for e.g. the follwing results cannot be shown
Workorder number Line Number ProdNo
3333 1 221
3333 1 221

For e.g the follwing results can be shown

Workorder number Line Number ProdNo
3333 1 221
3333 2 221


Any help, can u see what i am doing worng

 

[Matty]

What does the SQL of your query look like? Is it an update or append query (I'm confused from your thread title)?

 

[shaz123]

Sowi for the confusion

Sowi for the confusion, it is an update query, below is the SQL of the query

UPDATE AberdeenWOLines LEFT JOIN AberdeenWOLinesclient ON AberdeenWOLines.WorksOrderNumber = AberdeenWOLinesclient.WorksOrderNumber SET AberdeenWOLinesclient.WorksOrderNumber = AberdeenWOLines.WorksOrderNumber, AberdeenWOLinesclient.[Line Number] = AberdeenWOLines.[Line Number], AberdeenWOLinesclient.ProdNo = AberdeenWOLines.ProdNo, AberdeenWOLinesclient.ProductDescription = AberdeenWOLines.ProductDescription, AberdeenWOLinesclient.Quantity = AberdeenWOLines.Quantity, AberdeenWOLinesclient.[Cylinder Serial] = AberdeenWOLines.[Cylinder Serial], AberdeenWOLinesclient.Status = AberdeenWOLines.Status;

 

[neileg]

You are attempting to update the field that you are using to join the two tables here:

SET AberdeenWOLinesclient.WorksOrderNumber = AberdeenWOLines.WorksOrderNumber

That doesn't make any sense.

 

[shaz123]

Thanxs for your response neileg, but i am new to this, what i am trying to do is update all the fields from the AberdeenWOLines to AberdeenWOLinesclient, were the Worksorder number, Lie number and Product number are set to primary key. Were would i put the join between the two table if i need to update every field?

 

[neileg]

The two fields must already be the same if you have joined on them. That's why I said it made no sense.