Customer Database

Part I Creating the Customer database

  1. Create the two tables (Customer Table & Sales Rep Table) and enter the data for the tables

Customer Table

Field Name / Data type / Field Size / Primary Key / Description
Customer Number / Text / 4 / Yes / Customer Number (Primary Key)
Name / Text / 20 / Customer Name
Address / Text / 15 / Street Address
City / Text / 15 / City
Prov-State / Text / 2 / Province / State(Two-character)
Postal Code / Text / 7 / Postal Code / Zip Code
Balance / Currency / Current Balance
Credit Limit / Currency / Credit Limit
Sales Rep Number / Text / 2 / Number of Matching Sales Rep
Customer Number / Name / Address / City / Prov-State / Postal Code / Balance / Credit Limit / Sales Rep Number
AN91 / Atwater-Nelson / 215 Watkins / Brandon / MB / R7A 4E5 / $3,478.50 / $7,000.00 / 04
AW52 / Alliance West / 266 Ralston / Thompson, MB / MB / R8N 1S5 / 492.20 / $4,000.00 / 07
BD22 / Betodail / 542 Praire / Winnipeg / MB / R2V 3E6 / 57.00 / $4,000.00 / 07
CE76 / CarsonEnterprise / 96 Prospect / WinnipegBeach / MB / R2Q 5L8 / $4,125.00 / $9,000.00 / 11
FC63 / Forrest Co. / 85 Stocking / Edmonton / AB / T6P 1X2 / $7,822.00 / $7,000.00 / 04
FY16 / Fedder-Yansen / 198 Pearl / Yorkton / SK / S3N 4G2 / $3,912.00 / $7,000.00 / 07
LR72 / Lanross, Inc. / 195 Grayton / Saskatoon / SK / S7N 1Z5 / $0.00 / $7,000.00 / 07
RO22 / Roberston, Inc / 682 Maumee / Thompson, / MB / R8N 1S7 / $2,336.25 / $7,000.00 / 11
RO92 / Ronald A. Orten / 872 Devonport / Winnipeg / MB / R3P 0S7 / $6,420.00 / $7,000.00 / 07
GC01 / You r name / Your address / Winnipeg / MB / Your p code / Make up amount / Make up amount / 04

Customer Database

Sales Rep Table

Field Name / Data type / Field Size / Primary Key / Description
Sales Rep Number / Text / 2 / YES / Sales rep Number (Primary Key)
Last Name / Text / 12 / Last Name of Sales Representative
First Name / Text / 8 / First Name of Sales Representative
Address / Text / 15 / Street Address
City / Text / 15 / City
Prov-State / Text / 2 / Province / State(Two-character)
Postal Code / Text / 7 / Postal Code / Zip Code
Sales / Currency / Total sales amount of sales Rep
Commission Rate / Number, Single / 2 decimals / Commission rate
Sales Rep Number / Last Name / First name / Address / City / Prov / Postal Code / Sales / Commission Rate
04 / Wright / Cecilia / 125 Westchester / Winnipeg / MB / R4K 3G7 / $53,172.00 / 0.08
07 / Perry / Thomas / 1664 Birchwood / Edmonton / AB / T7R 8J4 / $74,956.00 / 0.06
11 / Sanchez / Maria / 722 Kreiser / Regina / SK / S4N 6G4 / $30,125.00 / 0.07

Part II Changes - Customer database

1.Name field size change from 20 to 25 characters

  1. In the Customer table, insert new row called
  2. Field name -Cust Type
  3. Data type -text,
  4. Field size - 3
  5. Description – Customer Type (REG, DSC, SPC)
  1. Customer Table, change all Customer types to REG except
    -Betodail Should be SPC
    -Fedder-Yansen should be DSC

Part III Forms - Customer database

  1. Create a Customer Update form to look similar to above

Save the form as “Customer Update”

  1. Add an Available Credit calculated field
  2. Both the Sales Rep and Cust Type are Combo Boxes

Create a new Table for the Customer Type

Type / Discount
REG / 5
DSC / 10
SPC / 30
  1. The Sales rep Combo box should include Reps number and First and Last names
  2. Set the Appropriate Row Source for these Combo Boxes
  3. Set the Tab order to: Customer number, Name, Address, City, State-Prov, Postal Code, Sales Rep Number, Customer Type, Balance, Credit Limit
  4. Lock Available Credit so the user can not change it
  5. Available Credit should be formatted to currency.
  6. Balance, Credit Limit and Available Credit should be right aligned.
  7. Be sure Customer Number, State-Prov, & Customer type are formatted for Upper case
  8. Add date and time to the form footer
  9. Add the Title to form Header “Customer Update Form”, you decide the font and point size
  10. Add yourname Corporation to the form header
  11. Add the two rectangle boxes as shown, you decide the Fill/Back colour
  12. Add a drop shadow Special Effect to the two rectangle boxes
  13. Add a Fill/Back colour to the form
  14. Balance Field Rule >=0 AND <= 20000
    Validation text Balance must be between $0.00 and $20,000.00
  15. Credit Limit default value of 7000
  16. Force the State/Prov to upper case and exactly be two characters long
  1. Create a relationship between
  2. the Sales Rep number in the SLSREP table and the Sales Rep number in the Customer table,
  3. Enforce Referential Integrity, so that only Sales rep numbers used in the Sales rep table will be valid in the customer table
  1. Use this new form to add YOUR OWN NAME as customer to the database, other than your name you can make up the rest of the data

Part IV Reports - Customer database

  1. Create a report that subtotals the balance for each Sales rep
  1. Include appropriate headers, footers, etc.

  1. Create a basic graph showing each customers balance,
    Save as Customer Balance Chart

Customer database (ver 2.1) Page 1 of 5