Thank you Dennis for your prompt reply. You are obviously more experienced
at db design than I and I will have to sit and chew on your suggestions. I
would hope that I can get back to you in the future with any questions.
Also, I am doing this in my free time so can't always be timely in
communicating.

That said, I have a couple of questions about the first suggestion you made,
with apologies for my naivety.

"Dennis" wrote:

Here's what I'd do:

Create a "first-time only" startup screen that asks them what their
city/county/whatever is. You could also ask for their phone number. I'd
create some kind of unique key based on some compilation of that information.
I'd also store all that information in a little stand-alone table in their
copy of the database

As an experiment, I did create a list of counties and towns which I
extracted from official gov. lists and cleaned up in Excel, then added
columns with abbreviations for each. I then imported that to Access, which
created two tables tblTown (with a column "Lookup to tblCounties") and
tblCounties (with sublists of all the towns for each county). Would this
suffice for the Key you suggest above? Those tbls could be part of the db.

(accompanied by the key you create).

I also created a county town code system based on a two letter for county,
three letter for town. Thus the designation for Leeds Historical Society
would be "AnLds", a cemetery would be "AnLds14" for the 14th cemetery on the
town list, both in the town of Leeds, Anderson county. Would this suffice as
the Key you suggest?

My thought was that this same AnLds plus a 5 or 6 digit number could be used
for the individual person ID, although I don't know if Access will
accommodate that type of mixed letter/number as a PrimaryKey. (Also, it was
suggested to me that a eleven digit number [county 12, town 127, individual
1-99,999] would be easier for Access to handle, but I would have to
investigate how that would be set up.)

If I understand you correctly, you suggest it is better to include the Key
in every table to insure a correct link?
If so, you say "a little stand-alone table" implying that the town and
counties should be in ONE? table with an additional field with this AnLds
code?
*************
Well, from this point on I will show my ignorance if I don't read up about
things like Public variables, etc though I have a sense that you are on the
right track.
Once again, thanks so much for all your time and creativity. R-

Now, in the main tables, EVERY TABLE has a column where that key goes.
When
your users first startup the application from this point forward, that key is
read and saved in a PUBLIC variable. Then, every time a new record is added
to any table, that key value accompanies it.



Okay, that's done. Now you need an "extract" routine. You should write a VBA
routine to extract all records and place them in a flat-file (.TXT) which can
be sent to you via email. The "header" for the flat-file contains all the
city/county/phone data, as well as the key. This will ultimately become your
"master" so that you can lookup data by city/county/etc. YOUR little
stand-alone table will have ALL those key-records from ALL the counties in
it. Now all the individual records follow. If there are multiple tables, each
record should have a field identifying by name, which table that record
belongs to. Your IMPORT routine would then parse this file as needed by
table, and do the puts.

On the counties' side, once a record were dumped, a flag would be set in
that record, indicating that it had already been sent to you. That would
preclude it from being RE-sent, unless the users said they wanted ALL records
to go again.

That should give you the appropriate functionality you seek, and only be a
few days of programming effort on your part. (Perhaps this is too
complicated, and others might have a simpler way. I've spent many years in
manufacturing shop-floor data-collection systems, and those require 100%
accuracy and redundancy. Because of that, my solutions tend to leave no room
for ambiguity as to record statuses or up/downloading methodology.)