Comp25: Access - Customer Orders

Learning Objectives

1. To enhance your customer database by keeping track of your orders, including the employees that sold them.
2. To establish Relationships between tables using the LookUp Wizard data type.

Warning:

When creating fields and tables, it is highly advised that you keep the exact same names shown in the assignment directions.  Pay careful attention to capitalization and spaces (or lack thereof), and do not skip any steps.  Failure to do this could cause a problem down the road which will require significant time to fix.

You cannot begin this assignment until you have finished Populate Some Tables.

Steps to Take

1. Using Access, edit your existing Company-Database.mdb file.
   
   Note: For this assignment, you are going to create dynamic drop-down menus for datatypes CustomerID, EmployeeID, and ProductID.  This will enable you to choose customers, employees, and products when creating new records in this table, seeing their actual name rather than just their ID number. This process will automatically create relationships between all four tables, which is critical to successful completion of future tasks.  
2. Create a new table in this database (in Design View) for your Orders.  Add the following fields with associated (data types) in parentheses:
   1. OrderID (AutoNumber)
   2. CustomerID(Lookup Wizard)
      1. CHOOSE: LookUp Wizard from the drop-down list of Data Types
      2. CHOOSE: "I want the LookUp Column to look up values in a table or query"
      3. CHOOSE: Customer Table
      4. CHOOSE/DOUBLE-CLICK: LastName Field
      5. CLICK: Finish 
      6. Save your table as "Orders" and let Access designate OrderID as your Primary Key.
   3. EmployeeID (Lookup Wizard)
      1. CHOOSE: LookUp Wizard from the drop-down list of Data Types
      2. CHOOSE: "I want the LookUp Column to look up values in a table or query"
      3. CHOOSE: Employee Table
      4. CHOOSE/DOUBLE-CLICK: EmployeeName Field
      5. CLICK: Finish & Save
   4. ProductID (Lookup Wizard)
      1. CHOOSE: LookUp Wizard from the drop-down list of Data Types
      2. CHOOSE: "I want the LookUp Column to look up values in a table or query"
      3. CHOOSE: Product Table
      4. CHOOSE/DOUBLE-CLICK: ProductName Field
      5. CLICK: Finish and Save
   5. OrderPrice (Currency)
   6. OrderDate (Date)
3. Populate your table with 60 orders, giving each Customer an average of 2 orders, some more, some less.  Use somewhat random (but realistic) prices and dates (throughout the current year) so that your reports will be interesting.  All fields for each order must be completed.
4. Edit your table again and change the Default Value of OrderDate to date() .  Create 5 more orders that you just sold today.  Close the Orders table.
5. Pull down the Tools menu and choose Relationships.  You should now see the tables of your database along with the relationships defined, linking the primary keys as you have requested when creating Lookups. 
6. Exit from Access and upload your work using Blackboard. 

Grading Guidelines

This assignment will be graded using the following guidelines: