Comp25: Access - Import Customers & Create Tables

Learning Objectives: Create three tables in your database

1. To start off your Customer Database using the customer list you created in Excel.
2. To learn how to import data from Microsoft Excel into Microsoft Access
3. To modify a table after it has been created by adding new fields and changing field properties
4. To get accustomed to using Access to enter data directly into a table
5. To create additional Tables within Access to track your employees and products
6. To learn more about Data types and Field properties

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 Customer Mailing List.

Steps to Take - Import Customer Table

1. Using Access, create a new database file and save it as YourCompanyName-Database.mdb to your U: Drive (or in a place you won't lose it).
   1. IF YOU ARE USING ACCESS 2007, BE SURE YOU SAVE YOUR DATABASE IN THE EARLIER ACCESS 2002-2003 .MDB FORMAT, NOT THE 2007 .ACCDB TO INSURE YOU CAN EDIT YOUR DATATBASE FROM EITHER 2002, 2003 OR 2007 VERSIONS OF ACCESS
      
      CLICK HERE FOR DETAILED Access 2007 instructions
2. Create a new Table using the Import Table feature. 
   1. Choose New
   2. Choose "Customer Mailing.xls" file located in your web-docs folder.  (You will likely need to change the "Files of Type" to .xls before you can see your file.)
3. In the wizard, be sure to read and follow each step carefully:
   1. Indicate that the First Row Contains Column Headings.
   2. You want to store your data into a New table.
   3. Name your fields exactly as follows:
      1. FirstName (no spaces, same capitalization)
      2. LastName
      3. Address
      4. City
      5. ST
      6. ZIP
   4. Do not import (skip) the Sales, FullName, or Locale fields.  You'll need to click on each of these columns and choose Skip.
   5. When prompted, let Access add its own Primary Key.  It is going to call this field "ID".  We'll change it later to "CustomerID".
   6. Call your new table "Customers".
   7. Finish the wizard and open your newly imported table in Datasheet view.  To simplify our assignments, delete all but the first 25 customers.
   8. Notice the ID field that was created automatically during the import routine.  This is an AutoNumber data type that represents your customer number, and is unique to each record.  This is the "primary key" that will be used to link Orders to Customers.
   9. Make the following changes to your new table using Design View:
   1. Add a new field called PhoneNumber and give it a proper Input Mask using the Input Mask Wizard (CLICK the button with "dot dot dot" next to the field properties).
   2. Give reasonable Field Size limits to each of your fields (for example, ST should only be 2 characters).  Remember that the ZIP field isn't just five digits, it can sometime have nine plus the extra character for the hyphen.  Same with the PhoneNumber.
   3. Decide which fields should have a Default Value.  Can you assume that most of your customers will be from the state CA?
   4. Rename the ID field to "CustomerID" (no spaces).
   10. Save your changes to the Customers table.
   11. Return to Datasheet view and populate the new PhoneNumber field of each of your 25 customers with fake phone numbers.  Make sure your Input Mask works here.
   12. Edit your customer names so they are unique to your database.
   13. You just got three new customers!  Using the Datasheet view, create three new records at the bottom of the table. 
   14. Return to the Design View
       1. Revise your Customers table and add a new field called CustomerType. 
       2. Make this field a drop-down menu by setting the data type to Lookup Wizard.  In the first step of the wizard, choose "I will type in the values that I want". 
       3. In the next step, you want just one column.  Here, you will create two rows with these values: Wholesale and Retail. 
   15. Return to the datasheet and edit each of your existing customers and assign them a Customer Type (the majority should be Retail, but make a few of them wholesale). 
       1. The default value does not affect records that were already created, only new records. 
       2. When assigning Retail or Wholesale, you can speed up the process by pressing R or W on your keyboard, since the drop-down menu will automatically fill in the rest.
   16. Return to the Design View.  Make Retail the default value of the CustomerType field. Save and return to the datasheet view
   17. Create a new customer with realistic contact information, and watch the default CustomerType in effect.

Steps to Take - Create Employees and Product Tables.

1. Create a new table in this database (from Design View) for your Employees.  Provide these fields:
   1. EmployeeID (datatype: autonumber)
   2. EmployeeName (text)
   3. CommPercent (datatype: Number, Field size: Single, Format: Percent)
   4. EmployeePhone (text)
   5. EmployeeEmail (text)
   6. HireDate (datatype: Date/Time)
   7. Active (datatype: Yes/No)

   Save your table as "Employees" and allow Access to designate EmployeeID as your Primary Key.

2. Edit your Employees tables in the Design View.
   1. Use an input mask on EmployeePhone  to make data entry easier.. Use the Input Mask Wizard (Click the ... button) and choose Phone Number.
   2. Type your own input mask for CommPercent:
      1. Use an input mask on CommPercent to make data entry easier.   TYPE: .00% for the input mask property (since the Input Mask Wizard doesn't have a setting for percentages).
   3. Use 0.15 as the default value for the Commission percentage and set the decimal places to zero.
   4. Use the Caption property to make your field names appear friendlier when you are viewing your table, and later when you create Forms and Reports. 
3. Now populate your Employees Table:
1. Add your employees names, their contact information, hire date (spread out over the last year), and the percentage commission they will earn on each of their sales.  (If you need to specify a single-digit percentage, enter it with a leading zero like 05.) 
2. You must have a minimum of 5 employees. 
3. "Fire" one of your employees by un-checking their Active field.
4. Switch to the Design view and edit the Employees table
   1. Change the Default Value of HireDate to date() 
   2. Go back to the datasheet and add a brand new employee that you just hired today.  Notice how the default value automatically shows today's date.
4. Create a new table in this database (from Design View) for your "Products".  Provide these fields:
   1. ProductID (datatype: autonumber)
   2. ProductCode (text)
   3. ProductName (text)

   Save your table as "Products" and allow Access to designate ProductID as your Primary Key.

5. Edit your Product table in the Design View.
   1. Give reasonable Field Size limits to each of your fields (for example, ProductCode should only be 5 characters).
   2. Use the Caption property to make your field names appear friendlier when you are viewing your table, and later when you create Forms and Reports. 
6. Now populate your Products table:
1. Add your product names and codes. 
2. Product Codes are codes or abbreviations for each of your products, so if one of your products was a 12 Pack of Mountain Dew, the code might be MD12. 
3. You must have a minimum of 5 products.
7. Exit from Access.  Upload your database file using Blackboard. BE SURE YOUR DATABASE IS CLOSED AND ACCESS IS CLOSED BEFORE YOU UPLOAD TO BLACKBOARD!!

Additional Resources

• Access Tables
  • Data Types
  • Field Parameters
• Importing Data

Grading Guidelines

This assignment will be graded using the following guidelines: