My preference is to add a TerminationDate field. That way, I don't have to
find other tables or other databases to pull up historical information.

By the way, your tblMembership doesn't appear to include "members". Do your
"and more..." fields include either a [FamilyID] foreign key or a
[IndividualID] foreign key?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Kathy R." wrote in message
...
I am creating a membership database for a church. Individual information
is in three basic tables:

tblFamily
FamLastName (for entire family)
Address

tblIndividual
FirstName
MiddleName
LastName (takes care of hyphenated married names, or different names for
children, etc.)
BirthDate
MarriageDate

tblMembership
JoinDate
JoinManner
TerminationDate
TerminationManner
and more...

Once a person leaves the membership of the church I need to "archive" the
information. It needs to be kept for our permanent records. What is the
best way to do this? My two thoughts would be:

1) set up duplicate tables and move the "archive" information to it

or

2) Create an active/not active field in the tblIndividual and keep all the
information, both active and archived in the original set of tables.

Is one way or the other better or more efficient? Is there some other way
that I haven't thought of that would work better?

Thank you for your help and input!

Kathy R.