Append query question

[tmreic]

I am currently using the append query method to update a table from one database to another. My question is, I would like to find a way to display and capture the records that were not updated due to key violations? The key violations occur due to enforcement of referential integrity. I am updating a table on the many side of the one to many relationship. I would like to retrieve this information so that i can correct the table on the one side of the relationship. In example below i am using the append query to update table2. My errors occur if i am trying to append records in which the recieving ID is not present in Table1. I would like to capture these records somehow so that i can update or fix the errors in table1 with the missing Recieving ID.

Table1: 1 side of relationship
Sample barcode: primary key
Recieving ID: Indexed, no duplicates, required

Table2: many side of relationship,
MillTrackingID: primary key
Recieving ID: Foreign key, required

Thanks,
Tim

 

[psemianonymous]

Make a "find unmatched" query where you find all records in Table2 which don't have a matching record in Table1. There's a wizard to help you make the query for you. --
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

 

[tmreic]

Foolio12,
I tried your suggestion but it did not work since my records were not appended to table2 in the append query. The append query only added records to table2 that had a Receiving ID in table1. The records that had a Receiving ID that was missing from table1 were not appended. Table1 and Table2 are in one database and the append query and the table used in the query are in another database.

I guess I could compare table1 versus the table that I using to append my table2 and find the unmatched records. I was trying to find a way to do it all at once.

Any other suggestions?

Thanks,
Tim

 

[psemianonymous]

You can link to the table in the second database as a "linked table". Then you can make the "find unmatched" query based on the local table and the linked table. This will show all the items.

I don't know what you mean by "doing it all at once". If the record is not in the main table, you can't append to the secondary table. I just don't know what you mean. Do you want to automatically add stuff to the main table, THEN add to the secondary table? That can be done, if you want to. --
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

 

[tmreic]

I will try to further explain what i mean when i wrote "Doing it all at once".

When i perform the append query i get a error message stating that not all of the records can be appended due to key violations. Instead of first trying to figure out which records are violating rules. I went ahead and hit yes to append the records that can be added to the table.

What i would like to have happend is the records that were not appended come up on the screen,or be exported to excel, or go to a new table. That way i can easily isolate the records that are violating the referential integrity rules.

In the mean time i can use your idea of running a "Find unmatched" query to isolate the non-appended records as a starting point.

Thanks for your help and suggestions,
Tim