okay i will try to keep this short, sweet and non-confusing.

I had one database...i made two copies of it so now i have 3 databases. I want each AREA of my organization to have their OWN copy. There will be a MASTER copy that we have that has ALL employees. The other two copies will have only certain employees filtered by their organization code. I got all that to work fine. The problem is the main database which is a work tracking database. I have used VBA to import all the records into a table for each resource area. (ex / table1 has resource area 1's records, table2 has resource area 2's records...and the main work tracking table needs to get those records into it). So I got the importing to work great. On Open it pulls the records into the tables. Now I got to APPEND the new records from each (table1, table2) into work tracking, AND update any records that may have changed. So I created an APPEND TABLE QUERY by using the Find Unmatched query wizard and created an UPDATE query where it locates all records with matching RECORD ID's (the primary key for all 3 tables...an autonumber field)....the problem is this: The people working in the main work tracking and the people in the resource areas all creating records at the same time in multiple databases are creating many of the same AUTONUMBER fields so when it goes to update or import new records....many are getting missed or updated with the wrong information. I dont have anything else in the tables 17-18 fields that can BY ITSELF distinguish the records as unique. Is there a way to make the query find records that are unique (Find Unmatched) based on ALL fields....in other words it looks at the WHOLE record and compares ALL fields and only if the record is unique in that way does it append it...and how do i make it not update over the top of a totally different record that has the same record ID...very difficult for me.... any help would be appreciated :(

Hi,
To answer your question, Yes, it is possible to write code to compare each field in a record and do appropriate appends and merges.

But, that is really not the question that will give you a good solution. The issue is that your database structure is not designed for manual or automatic replication. As you recognized, you don't have a field that will uniquely identify and separate records from the three different tables.

One manual approach is to have two field primary key. One would identify the source and the other would be the auto number.
If you choose to adopt this method, I recommend that you use short and descriptive field names and identifiers. Check that you are using good naming conventions and avoiding special characters and reserved words. You don't want to compound your challenges unnecessarily.|

Good luck.
Teresa

DataDynamicsNW.com
Coordinating Lead Author, Access 2003 VBA Programmer's Reference