Append Query on Concant key

[Praxden]

Hello,

I am trying to update a recordset of documents to relate them to a different aircraft. I am using a select query to select the document set that I want to copy to the new aircraft. The table I am trying to update to has two fields for the primary key. I am setting the Append query to the document set and the second field to the new document number. All I'm getting is a key violation error. The tables are as follows;

tblDocument
docnumber

tbllinks
docnumber
acnum

tblAircraft
acnum

I don't want to remove the relationship from the documents to the old aircraft. I'm trying to copy the old aircraft documents to a new aircraft. Any help would be appreciated.


Praxden
jbrooks@triad.rr.com

 

[JerryDennison]

If the new aircraft uses the same document number as another aircraft the only common denominator is the document number. So, in the link table all you need append are records that have the new aircraft number with the existing document number. How are you trying to do this now?

 

[Praxden]

Jerry,

Currently I have it set up so the document page has a subform that shows the related aircraft. The problem is there are more than 200 documents related to an aircraft. To manually go thru each document is very time consuming. A number of these documents will remain the same for specific groups of aircraft, therefor I was trying to update the whole group to a new aircraft then remove the documents that don't belong.

 

[JerryDennison]

This can be done with an append query. You must first have the aircraft added to the aircraft table. Then use an append query to add a record for every document to the intermediate table for the new aircraft. To do this you have both the aircraft table and document tables in your query. Do not join them. Add the aircraft ID field from the aircraft table to the QBE grid and the document ID field from the document table to the QBE grid. This will give you a record from every aircraft with every document. Limit the aircraft to the one you want via criteria. Change this query to an append query selecting the intermediate table. In the append to property select the aircraft id link field for your aircraft id and document link for the document id. When you run the query it will add a record for every document in the intermediate table for the aircraft you selected.

 

[Praxden]

Jerry,

Thanks. It worked great. I wasn't setting the appends table up right. I appreciate your help.


Jim Brooks

 

[JerryDennison]

You're welcome.