Hi Everyone,

Your feedback with my last question (especially user Ajax) was immensely helpful and I am on my way to building my database as I want it. I am currently working with a smaller database to test the theory and had a question. Here is the background;

I am not splitting my database because our use of multiple offline databases would make it too messy. Instead I am using similar copies of the same database with additional fields to run a series of append and update queries. Both fields in the satellite database and the master database tables will be identical. The important ones are as follows.

ID (Autonumber - Primary Key - Random Long Integer), EmployeeID (Number but is an AutoNumber in the table where it is the primary key), Modified Date (General Date - Defaults to now when record is created), Appended Date (General Date - Will be used in append query), repID (Replication ID - AutoNumber).

I know that I don't want to use the Replication ID as the primary Key as it slows things down. Is it necessary to have both the Random ID Autonumber (Primary Key) as well as the Replication ID?

When I run my append query it appends records which have a Null field for Appended Date, and then it appends those where the modified date later than the appended date (if there is one). After that I run an update query to set the appended date to (now) in case I ever modify those records again. I can't think of a better way to do this.

Perhaps I could leave the primary key out of my append query and let that auto-fill incrementally in the master database? That seems like it would make things less messy because I'm still using a replication ID that should be unique every time. Will this still work if I occasionally delete data from the satellite database then continue to collect and append new data?

I hope this explanation makes sense. I was having a world of hurt until I realized I needed to append parent tables with highest referential integrity first.

Thank you!