Combining several databases into one master

[Sammy]

I'm using Access 03. I have created a new customer contact database and will
give each of the 5 sales reps a copy for their laptops. Every 3 months we
need to combine the reps databases into a Master database so that our central
office will have access to all customer information. The database files
consist of two important tables, CONTACTS and ACTIVITIES. I use an
Autonumber for the ID field in the Contacts table and the Activities are
linked to each contact by this field. I know how to work with macros/append
queries, etc., but I need to know:

1. How do I deal with the AutoNum problem? Should I start each reps
autonumbers at 20,000 increments so that when they're combined there are no
duplicates? (It's doubtful they would ever go near 20,000 customers)

2. Are there any suggestions on how I should go about setting this up? I
have data to import for some of the reps, but others are starting with a
blank database. I have not distributed it yet, so I can modify the design if
necessary. I plan to import the individual tables into the Master and then
run an append query to combine.

3. I need a quick way for the reps to Export the two tables into a blank
database. Should I create a macro that runs a command (Transfer) for both
tables?

P.S. - we are not able to have a centralized database on the network for rep
access, they must be stand-alone files on their lapies. Any assistance is
appreciated.

 

[tina]

well, this sort of situation might be a good candidate for replication.
however, that should only be undertaken by someone with a trunkload of
patience and a headful of thick, healthy hair (to better tolerate the loss
of some <g>). i've never attempted replication myself, so i can't make any
recommendations.

as an alternative, you might *try* the following: you could use an
Autonumber primary key in the CONTACTS table of the dbs used by the sales
rep. in the main database, import the records into the CONTACTS table -
using a unique index made up of a LongInteger field (to hold the Autonumber
value from the sales reps' tables) and a field that identifies each sales
rep. the *combination* of those two fields will be unique. you can use the
two fields as a combination primary key - or you can use an Autonumber field
as the primary key of the main database's CONTACTS table, and the unique
two-field index to ensure that the records can be matched to the reps'
individual dbs.

hth

 

[Sammy]

Thanks Tina. I'm not familiar with using two fields combined together for a
primary key. I will give this a try. I will also look up replication.
Thank you

 

[tina]

you're welcome. suggest you check out microsoft.public.access.replication
newsgroup. it doesn't get a lot of traffic, but it is there. also see
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#Replication
and
http://www.mvps.org/access/tables/tbl0015.htm
also use the Search option (left side of this page) for more on
replication - i didn't check out all the links i got in Search result.
suggest you bookmark both websites - you'll find them invaluable while
working on any Access application. good luck with your project!