Design Assistance

[KarenK]

I had a database, and now I am redisigning based on what I learn here.

I am developing a database based on a table called “membersâ€. The premise
of the database is to communicate via mail merge to a representative of that
member, or spouse when they pass away. Mr. Smith passes, and we need to
communicate with the daughter who lives at a different address. We do not
know the address of the daughter until she writes in to us advising she is
next of kin. We then key that information into the Recipient Table.

However, there are many times, when a spouse writes in, and therefore there
is duplicate information entered because we reenter the name and address into
the recipient table. The reason this is done is the recipient table is what
we base our mail merge on.

What I would like to do is somehow merge(move) some of the member
information (Add1, Add2, City, State, ZipCode) into the recipient table to
avoid duplicity of typing.

I am not a developer, so the more information it is put into simple terms
the less I will need to question.

I appreciate any help out there.

My original database consisted of 3 main tables:

tblMember
SS# (PK)
Last
First
MI
ADD1
ADD2
City
State
ZipCode


tblRecipient
RecID (PK)
Title
First
Last
Add1
Add2
City
State
ZipCode
Relationship

tblTerminationInformation
TermID (PK)
MemberID (FK)
RecipientID (FK)
TerminationReason
DateOfDeath
DateOfCancellation
RefundAmount

 

[KARL DEWEY]

Use one table that has a self joined relationship.
tblMember
SS# (PK)
Member - Yes/No - default Yes
Title
Last
First
MI
ADD1
ADD2
City
State
ZipCode
Relative Number Integer Self join PK
Relationship
TerminationReason
DateOfDeath
DateOfCancellation
RefundAmount

Man is related to wife, wife is related to man. Child related to either man
or wife.

 

[Evan Keel]

In your scheme, how is RelativeNumber related to SS#?

Seems to me you wouldn't have the Social Security Number of the recipient.
So how would do a self join?
And why would you need a self join when the relationship between Member and
Recipient is one to one? This just be one of my dumb days..

For the OP, if a spouse writes in and a daughter writes in, just what data
is duplicated? And the relationship between Member and Recipient is one to
one ( a member has one recipient, a recipient is next of kin to only one
member?)

All the best,

Evan

 

[KARL DEWEY]

In your scheme, how is RelativeNumber related to SS#?
Relative is the field name and datatype is Number - Integer. In the
relationship window put the table twice. Access adds a sufix to the second
instance that is '_1'.
Set the relation from PK (SS#) to Relative and set Referentential Integerity.

The Member field is set to No for the relative.
If you do not have SS# how do you identify the recipient?

 

[Junior e Glezieli]

I had a database, and now I am redisigning based on what I learn here.

I am developing a database based on a table called "members". The premise
of the database is to communicate via mail merge to a representative of
that
member, or spouse when they pass away. Mr. Smith passes, and we need to
communicate with the daughter who lives at a different address. We do not
know the address of the daughter until she writes in to us advising she is
next of kin. We then key that information into the Recipient Table.

However, there are many times, when a spouse writes in, and therefore
there
is duplicate information entered because we reenter the name and address
into
the recipient table. The reason this is done is the recipient table is
what
we base our mail merge on.

What I would like to do is somehow merge(move) some of the member
information (Add1, Add2, City, State, ZipCode) into the recipient table to
avoid duplicity of typing.

I am not a developer, so the more information it is put into simple terms
the less I will need to question.

I appreciate any help out there.

My original database consisted of 3 main tables:

tblMember
SS# (PK)
Last
First
MI
ADD1
ADD2
City
State
ZipCode


tblRecipient
RecID (PK)
Title
First
Last
Add1
Add2
City
State
ZipCode
Relationship

tblTerminationInformation
TermID (PK)
MemberID (FK)
RecipientID (FK)
TerminationReason
DateOfDeath
DateOfCancellation
RefundAmount