customer data base w/family members

G

Guest

I am attempting to convert an old Q&A database to Access. I have been able
to get most of the data into an excel format to import into Access. I have a
"lead person" in the record to whom all the mail is addressed and then I have
the spouse and other family members in several records. Do I need to set up
one table w/just the lead person and a seperate table with the others? Often
I have promotions that need to be sent to each person in the household, or
select persons in each household according to their purchase level for the
year. What type of field would you recommend to set relationships between
the tables?

Example of current data in each record:
Name 1: John Smith
Address: 123 Any St. City: Anywhere St: SC Zip: 12345
Phone: 555-123-4567
Acct A: 12345 Acct B: 34525 Acct C: 23451
Name 2: Jane Smith Acct A: 45678 Acct B: 78956
Name 3: John Smith Jr. Acct A: 75645 Acct C: 25847

Thanks,
Donna Brown
 
G

Guest

What is the relationship between Customers and Accounts? Can more than one
customer have the same account number, i.e., is it a many-to-many
relationship?

Sprinks
 
G

Guest

Each person may have several account numbers and each is individual to that
person and the property it represents. IE. John Smith has accounts at CM,
GA, HS and CP (all are different acct numbers), his wife has accounts at GA,
ST and CP (again with different account numbers).
What I am trying right now (to see if will work) is assigning the lead
person a customer id in one table w/just address info and in another table
listing all individuals and referencing the customer id to each individual in
that household. I'm not sure what I'm going to do when I get to the point of
setting up a form for data entry.
 
G

Guest

OK, so Customers and Accounts are a one-to-many relationship, as is Household
to Customer. Since the lead person is the same type of entity (that is, a
person) as other household members, following database normalization rules,
it makes more sense to put all people in the same table, and distinguish the
lead person by a boolean field:

Households
------------------------
HouseholdID AutoNumber or Integer (Primary Key)
Address Text
City Text
State Text
Zip Text
Phone Text

Customers
------------------------
CustomerID AutoNumber or Integer (PK)
HouseholdID Integer (Foreign Key to Households)
FName Text
LName Text
LeadPerson Yes/No

AccountTypes
---------------------
AccountTypeID AutoNumber (PK)
AccountType Text ("CM", "GA", "HS", etc.)

CustomerAccounts
------------------------
AccountID AutoNumber (PK)
CustomerID Integer (Foreign Key to Customers)
AccountTypeID Integer (Foreign Key to AccountTypes)
AccountNumber Integer or Text

So there are two cascading one-to-many relationships--Household to Customer,
Customer to Accounts. This is similar to the Northwind sample database
relationship of Customer to Order, Order to OrderDetails. You could
implement a form in a similar way to how it is done in the sample database.

Hope that helps.
Sprinks
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top