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 :(

You could do that, but that may be a bit more than you might have to do.

One approach I might take with this is...

Have a field in the table, and that field gets updated a bit differently based on the database that is creating that record. So, in Database1, that field might be the Record Number, & "DB1". So it might look like this....

DB1-1 Sam Jones
DB1-2 Fred Smith

etc....

Then, you set that field to be indexed and no duplicates in the main database, then when you append, it won't write over existing records.

You can also, when appending the records, and a duplicate is found, check for what has changed on that particular record, and mark it like that.



John