New Database design

[KarenK]

I am trying to create a new database using existing information. I currently
have a table called "TBL_Members". This has SS#, name, address one, address
two, city, state, zip code.

There are times when correspondece needs to be sent to the member, and other
times to a different recipient. I have a table called "TBL_recipient" with
SS#, name, address One, address two, city, state, zip code.

My unique identifier is the SS#. I am looking to have a way to have
reciepient information filled with the member information if the recipient
information is left blank.

 

[Jeff Boyce]

Karen

Before you proceed down this path, consider another approach. What you've
described (duplicating data in more than one "identical" table) is exactly
how you'd need to do this, if you were using a spreadsheet! MS Access is a
relational database -- you won't get easy use of Access'
relationally-oriented features and functions if you insist on feeding it
'sheet data.

For example, it sounds like you could have a single table to hold addressing
(?contact) information for persons, and a separate table that lists
membership status. Depending on your situation/data/domain, you could
consider including the ID # of the "alternate contact" person in that
membership table.

If you'll describe a bit more specifically what the (in your) real world
situation is, the folks here in the newsgroup may be able to offer more
specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KarenK]

Here is my situation:

I work for a company that deals with the elderly. In many instances, we need
to do a refund of funds due to death of the member. In some instances, the
refund will go to the spouse, who lives at the members address. In other
instances, the refund will go to a child at a different address.

My database was built specifically for the purpose of sending back refunds
and creating a letter based on specific information entered (IE Date of
Death, paid through date). The letter is a mail merge from the Access
database.

I download weekly information from my host system which contains the member
information.

I have been having staff reenter the recipient information, even if it is
the same as the member information. This seems redundent to me, and I feel
there must be a better, less time consuming process.

 

[Jeff Boyce]

It sounds like you are describing a situation in which you/your staff need
to select an address to use. One way I've done this is to create an address
table, a "person" table, and a "junction" table that holds valid pairs of
person/address. This way, you could "assign" one address to more than one
person, without having to re-enter the address.

Or if you won't need a record of the address to which you sent someone's
refund, then you could get away with using just the address table to allow
the selection of an address to use, but not keep a record of that.

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP