Design for future merging

Oho! I don't recall you mentioning "related records".

That will throw a monkey wrench in, unless you also tag those related
records with their respective locations.

And yes, you could still have an Autonumber field on your new, merged table
.... but it would be its own thing, unrelated to the original (auto)numbers
from the field.

Good luck!

Jeff
"Rlong via AccessMonster.com" u58125@uwe wrote in message
news:a6f8e28a53211@uwe...
Hi Jeff,

Great...really helpful! You're not misunderstanding--you're right on
track.
The combined file doesn't yet look like that because the ID field is,
indeed,
an autonumber. So, if I understand you when you say:

"When I appended the records, I'd be putting the ID numbers into a new
Numeric/LongInt field (that's the counterpart/equivalent of what
Autonumber
generates)."

..you mean that this new field would end up getting the original
autonumbers
from the various locations, but the Autonumber ID field in the combined
file
would write new autonumbers to any records that were added from other
locations.

So, this leads me to wonder what happens to all the related tables for the
various locations, because all the PK/FK links are using the autonumber
field
as the PK and linking to this autonumber for the FK. Is there a way to get
all the FKs to cascade the changes (I've seen reference to cascading) that
will be introduced when the locations are assigned new autonumbers in the
combined file? Or, will I need to migrate the PK in each table to the new
Numeric/LongInt field that I've created?

Again, thanks...



Jeff Boyce wrote:
Maybe I'm misunderstanding...

Here's my view of the data in your combined db:

ID Location blah, blah, blah

1 East
2 East
3 East
2 North
3 North
1 West
2 West
4 West
1 South
2 South
3 South
4 South
99 South

Notice that you can have multiple identical IDs (but also note that these
CAN NOT be autonumber type in the combined table!), distinguishable by
their
Locations.

To do this, I would leave the Locations/sites doing their thing with their
own Autonumbers, then use a query to append the records into my "master"
table. When I appended the records, I'd be putting the ID numbers into a
new Numeric/LongInt field (that's the counterpart/equivalent of what
Autonumber generates).

Again, DO NOT attempt to append autonumbers (from Locations) into an
autonumber (in main table) field ... and Access won't let you duplicate
an
already used Autonumber, so this might be moot!

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

Thanks, Jeff. Yes, I am using 2007. I'm not sure what the "replication"
feature was, but it sounds like it isn't an option anyway for 2007.
[quoted text clipped - 39 lines]

Thanks

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/201004/1