Friends,

I have a problem with my update query.

First thing, I have three tables in my database, WKO1 (this is linked to another database), Quality Defects, and Panels.

I pull four fields from WKO1 and add them to Quality Defects. One of the fields is [Status Code]. The values for this field can be "Released", "Closed", "Costed", "Complete", and "Unreleased".

These values change in the linked table so I run an update query to change them in the Quality Defects table.

The problem is that the PK for the Quality Defects table is carried over to the Panels table as a FK, and when there are records in the Panels table, the update query says that it could not update X records due to key violations.

The deal is though that when I ignore this message, all the fields are updated correctly.

Is this message a problem or can I ignore it with no future consequences to my database?

Thanks.

Linked tables aren't real tables, they're images of tables in other databases. So if you change the data in db2, your data in db1 should change. Have you tried verifying this rather than running an update query?

David,

I guess I did not explain myself well enough.

My linked table WKO1 contains about 1500 records. I use an append query to take records from table WKO1 where [Status Code] = "Released" and add them to table Quality Defects. I have a query related to a combo box on a form that only shows Work Order's that have a [Status Code] = "Released". This combo box is related to table Quality Defects, so I must update the records in table Quality Defects when their [Status Code] changes in WKO1.

I hope this is a little more clear.

Thanks.

Ok, different problem you have there. But you're still encountering difficulties because of your structure.

Why are you making a table of duplicated data when you already have it right there in WK01?
Make a query with the fields you'd like to have in your new table, and set the criteria for [Status Code] to "Released". You can even have your combo box pull from one column of this query, instead of from your base table. By storing data in two places you're creating a nightmare for yourself, unless I am totally understanding the reason for the second table. If it's because of additional fields in your new table that aren't in the original one, consider a one : one relationship that includes all fields from WK01 and any matching records from the 'sub' table with the extra fields.

Read up on database normalization if you haven't already. I'm sure everyone is willing to help but it will be easier if you're working with the proper structure from the start (note: you can fix existing data to a new structure, but it may take a little work if your db is already deployed).

HTH,
David R


[This message has been edited by David R (edited 04-15-2002).]