How to share and combine database info?

[R. Lavigne]

I'm a new user of Access (97 I believe). I created a
simple database containing project contact info - Name,
phone#, address, etc. I want to give a copy of my
database design and info to each of three other agencies
and let them add new records with info about their own
projects. After they've added their info, I want to
combine them all into one database and redistribute copies
to all, which now contain all project info for all 4
agencies. We're not networked, so updates will have to
come on disk. What's the easiest way to do this?

 

[John Nurick]

Something like this:

1) If you have any Autonumber fields in the database, change their size
from the normal Long Integer to Replication ID. This means that every
record created will have a random ID number, with minimal chance of a
record created at one site having the same ID as one created at another.

2) Add fields where necessary to store the agency to which the record
relates (and probably the date it was created and the person who entered
it). This makes it vastly easier to resolve discrepancies and
duplications in the data.

3) Think carefully about situations such as:
(a) There are two people called John G Smith at different addresses. Can
your database tell them apart?
(b) Agency A and Agency B each create a record for the same person (i.e.
two records that are identical but for the "Agency" field I suggested at
(2). What do you do?
(b) Agency A creates a record for John G Smith at 123 Acacia Avenue,
Anyville. Agency B creates a record for J Smith at the same address.
What do you do?

4) Once you're sure you'll be able to reconcile any discrepancies that
appear (e.g. with a couple of telephone calls),
(a) use Save As to make a copy of your database (structure only)
(b) send this, with instructions, to each of the agencies
(c) make a backup copy of your main database.
(d) when the results come back, open your main database and use File|Get
External Data|Link to access the data in one of the Agency databases as
linked table(s).
(e) create an Append query to append the data from each of the linked
table(s) to the real table(s)
(f) delete the linked table(s) from the main database
(g) link to the next Agency database's table(s). If the table name is
the same you'll be able to use the same append query.
(h) repeat till done.

5) Use some Find Duplicates queries to identify potential duplicate
records. Resolve these and other discrepancies.