Using MS Access/Getting Started

Advertisement


Question
I just recently completed an online Access 2007 class through my local community college but I still have questions about setting up a database for my specific business. I have a direct marketing business and I sell product at party's, vendor fairs and from my website.  Not all of my contacts are customers.  Some of my contacts are hostesses and other contacts just want to be kept update when specials.  I know all the information I want to track but my biggest area of confusion is in building relationships. Attached is the information I would like to put into a database:

Purpose - Keep track of: People – Customer or Contact, their information including how I meet them (party, vendor fair & at which fair, BBW etc) Party’s (Hostess, Guests Invited, Guests Attended, Party Sales Total, Hostess Benefits Received, and Party Mileage), Customer Orders, My Orders Inventory, Customers communications.

Customers and/or Contacts:
  Name - First Name, Last Name
  Mailing Address – Street, City, State, Zip
  Billing Address – Street, City, State, Zip
  Phone Numbers – Home, Cell, Work
  Email Address
  Birthday
  Anniversary
  Initial contact – Where/How did I meet this person
  Notes

Events:
Question – Should I keep “Party’s” separate from “Events” since Events can be several for a couple of with different hours set on the different day (Friday 5pm – 10pm, Sat 9am – 10 pm, Sun 11am – 5pm) and Party’s are held on a day for only a few hours (Fri 6:00 pm)?
  Hostess – Which could be a Customer/Contact
  Event Name – YMCA etc
  Event Contact Person – This person often is not a customer or person interested in information on my product for personal use
  Event Date/s – Events could be more than 1 day
  Event Time/s – Events could be different times for 2 consecutive days
  Event Address – Street, City, State, Zip
  Phone Numbers – Home, Cell, Work
  Email Address
  Guest Invited – Name of guest invited if it’s a party
  Guest Attend – Did the guest attend the party – Y/N

Orders – Customers and my own personal orders for personal use and inventory:
Question –Orders are usually for more than 1 item at a time, how would I entry that information?
  Customer Name or my name
  Order Date
  Category – Product size
  Items Number
  Item Description
  Quantity
  Price – Retail Price
  Subtotal
  Shipping & Handling
  Tax
  Total

Inventory – I want to keep track of the items I purchase, which sometimes are at discounted prices
Questions
  Category
  Item Number
  Item Description
  Quantity
  Price – I paid for the item



Loyalty Card – I offer a Loyalty program allowing my customers to receive free product
  Effective Date of the Loyalty Card
  Number of stamps when the Card was issued
  Dates when new stamps are added and how many stamps added
  Date the card was redeemed
  What the product was redeemed for

Customer/Contact Follow-Up – I want to track follow-up contact
  Date follow-up contact made
  How the person was contacted – Phone, my personal email, letter, text, group web email
  Why Contacted - After Party/Event follow-up, After Order Delivery, 3 months, 6 months, Hostess Coaching
  Response
  When to contact next

I know this is TO Much Information and I hope it is clear, if not please let me know.
Thank you in advance for your help
Brenda

Answer
Ok, Making a list of what you want to capture is a great first step. I would create the following tables:

tblPeople
PersonID (Primary Key Autonumber)
Salutation (Mr, Ms, etc.)
FirstName
Middle
LastName
Suffix (Jr, Sr,etc.)
Email
DOB
Anniversary
Notes
MailingAddressID (Foreign Key)
BillingAddressID (FK)


Note: I'm making an assumption here that some of your people can share an address If that is a possibility, then I would have a separate Address table"

tblAddress
AddressID (PK autonumber)
StreetAddress
City
State
Zip

I would also have a separate phone table
tblPhone
PhoneID (PK autonumber)
PersonID (FK)
PhoneNumber
PhoneTypeID (FK)

Phone type would be pulled from a lookup table:
tluPhoneType
PhoneTypeID (PK autonumber)
PhoneType (Home, Work, Cell, etc.)

Continuing with people I would add:
tblContact
ContactID (PK autonumber)
PersonID (FK)
ContactDate
ContactNote
NextContact
to record each contact with a person including the initial contact. You might also add a ContactTypeID FK from a lookup table to categorize contacts (i.e Phone, e-mail, referral, meeting etc.)

Next lets deal with the events:
tblEvent
EventID (PK autonumber)
HostessID (FK to PersonID from tblPeople)
EventName
AddressID (FK)

Its not clear whether an event can have multiple guests. I'm assuming there can so I would use:

tblGuest
GuestID (PK Autonumber)
EventID (FK)
PersonID (FK)

Notice I didn't include phone and email info in tblEvent. I'm assuming that would come from the from the Hostess so you would pull them from the Phone table using the PersonID.

Then you would have:

tblEventSchedule
EventScheduleID (PK autonumber)
EventID
EventDate (date and time)

Now lets deal with orders:
tblOrder
OrderID (PK Autonumber)
PersonID (FK) -- customer
OrderDate
EventID (FK) -- If the order was made at an event
Shipping
TaxRate

To record the items ordered you would use:
tblTransaction
TransactionID (PK Autonumber)
OrderID (FK)
ItemID (FK)
UnitPrice
Quantity
TransactionTypeID (FK)

tluTransationType
TransactionTypeID (PK Autonumber)
TransactionType (i.e. Purchase, Sale, etc.)
Direction (Incoming, Outgoing)

And that means:
tblItem
ItemID (PK autoNumber)
ItemDescription
UnitPrice
CategoryID (FK)

tluCategory
CategoryID (PK autonumber)
Category

Now we have the Loyalty program
tblLoyalty
LoyaltyID (PK autonumber)
PersonID (FK)
EffectiveDate

and
tblLoyaltyTransaction
LoyaltyTransactionID (PK autonumber)
LoyaltyID (FK)
TransactionDate
Stamps
Type (addition/redemption)
OrderID (FK)


I think that does it. Notice the naming convention for tables, Primary keys and foreign keys. Your relationships are from PK to corresponding FK.

If you have any questions on this, please feel free to ask.

Hope this helps,
Scott<>

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.