customer data base w/family members

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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
 
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.
 
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
 
Back
Top