Assignment is subject to corrections and changes! If printing this assignment, please be sure to check the online version for changes and to access any links

DUE DATE: See Laulima, Tasks, Tests & Surveys
POINTS: 45 points
SUMMARY:Modify a database, import data, create relations, queries.
SKILLS NEEDED:
  • Critical Thinking!
  • Tables: Create a table in design view defining different types of data. Import records. Create relationships between tables
  • Forms: Create a form with wizard. Modify form in design view. Enter data using form
  • Queries: Create selection queries in design view with criteria for various types of data. Create a calculated field.

Assignment is subject to corrections and changes! If printing this assignment, please be sure to check the online version for changes and to access any links. PLEASE ALWAYS LOOK BACK AT THE WEB VERSION WHEN COMPLETING THIS ASSIGNMENT.

INCOMPLETE ASSIGNMENTS WILL NOT BE GRADED! You will be asked to resubmit the assignment. This assignment must be complete in order to begin the next software assignment. ASK FOR HELP if you are uncertain how to complete steps!

NOTE: The learning exercises must be completed and submitted BEFORE this assignment. If software assignment is turned in without the learning exercises being completed, the learning exercises will be give a score of 0. Even if you think you know how to do things, you might not know the most efficient method. Do the learning exercises and learn!

Preliminary: Open the file CrazyComputers-yourname.accdb from your ICS101 database folder. If there is a security warning click the Options button and select Enable this content.

  1. (10 pt) Create a table called customers in DESIGN VIEW.
    Select Design View from the View icon
    This screen shot shows where the field information should be entered. Scroll down to see the actual fields to enter.

Do not enter information inthe description field, size & other information is below

Type in field names exactly as shown - be sure to include field names, types, and sizes, formats. Note: This is the information that needs to be entered. This is NOT how it looks in Access. See the screen shot above for where to enter the information in Access.

field name type field size

Additional information (NOT the description)

Customer ID number long integer
Specify as the Key (select field, then click on key icon)
First Name text 20
Last Name text 20
Street text 30
City text 15
State text 2
Zip text 5
Phone text 14 Specify Phone number for input mask (in properties)
click on Three dots at the end of the input mask line
First Purchase Date  date
Format short date
Mailing List yes/no

  1. (2 pts) Create an input form using the form wizard - select all fields and justified layout.
    1. Adjust the form in design view. The form should look like the form below. All fields in the record should be displayed on the screen at one time. Logo picture is CCC-Logo from Project 1
      Notice that the address is arranged as you would normally see an address this is a good practice when designing forms - present fields in a way that people are accustomed to seeing.
    2. All fields should display the full contents.  If a field is too small - make it larger. You may have to go back to this step after entering data in the next step. Once you input several records using the form, you can better see if all the contents will be displayed.
      Form layout
      If you close the form while working on it. Right click on the form and select Design View to go back to working on the form.
  2. (2 pts) To enter data using the form switch to Form View.
    Form view from the view icon
    Note: If you closed the form after creating it, just double-click to open the form in Form View.
    1. Use the data below and place it in the appropriate fields. You should be able to figure out which fields to use for which information. Be sure to select New Record for the second record - do not type over the first record. (2 points for each record and 1 pt for entering the correct codes for the first record.)
      Customer
      ID
      Name 
      Address Phone First Purchase Date Mailing List
      1998 For the first name enter the code  from lesson on Database Concepts
      For the last name enter the code for lesson on Database Queries
      321 4th Avenue 
       Kaneohe, HI 96744
      (808)235-1234 11/30/2011 Yes

      1999

       

      Enter your first name for first name and enter your last name for the last name.
      1 Akamai St. 
      Kailua, HI 96734
      (808)261-0000 1/31/2012 No
    2. Go back to Design View and adjust your form, if needed, so that all the full width of the data can be seen
  3. Import more records from a spreadsheet.
    1. (2 pts ) Import the file Morecustomers.xls (should be in your database folder) into the customers table in your Access file by following these steps:
      1. From the External Data tab click on Excel
      2. In the dialog box click Browse then find the file, Morecustomers.xls
      3. Click the second radio button labeled Append a copy of the records to the table from the drop down list select Customers
      4. Click OK
      5. In the dialog box that appears click Next
      6. Make sure there is a check nest to First Row Contains Column Headings then click Next
      7. Click Finish
      8. If you receive an error message, check common import problems or email the instructor for help
  4. (4 pts) Create Relationships. Create relationships in your data base as shown below. Be sure to enforce referential integrity.
    relationship chart
    If you can't connect or enforce referential integrity in the Customers and Customer Sales tables, then you Customer ID field is not defined correctly. Go back to the Customer's table in design view and make sure you have it defined correctly. (Right-click on the table and select Design view.)
  5. Create Queries. Use Query Design View to specify the criteria.
    1. (4 pts) Create a query to find all stock with a price that is over $50 and under $100. Include Part name, Part Description and Price in the query. Save the query as Midrange.
    2. (3 pts) Create a query to find all customers with yes in the mailing list field. Include all the fields needed to create an mailing address label.  Save the Query as Mailing list.
    3. (4 pts) Create a query to find all customers who have been customers over 5 years as of December 31, 2014 (Hint: Use the first purchase date. Be sure to use critical thinking!).The query should contain the customer's full name and phone number and first purchase date Save the query as Over 5 Years.
    4. (5 pts) Create a query to find all vendors that are out of state. The query should include Company name, state, and work phone. Sort the results by Company Name. Save the query as Out Of State.
    5. (5 pts) Create a query using the Stock and Vendor tables. Include the fields Part Name, Part Description, Type, Cost,  Price, Vendor Company Name. Using Builder add a calculated field to calculate the difference between the Price and the Cost and name the calculated field Difference. (The default name is Expr1 - rename it.) Save the query as Parts List.
    6. (4 pts) Create a query that lists all customers who have purchased the item CRZ-2000 COMPUTER. Query should include, customer name and all the fields needed for a mailing label and the part name. Note: because the stock and customer tables are related through two other tables (customer sales and line items) those tables must be included as well. Save the query as CRZ-2000 Customers.
      If completed correctly, this query will display 4 customers.
  6. Database files tend to become rather large and sometimes are not stored effectively. To decrease the size , it is important to compact the database. (Skip this step if you are working on campus. It doesn't work on the network drive)
    1. Click on the File tab then select Compact & Repair Database on the right hand side. ( If using Office 2007 - Click on the Office button then Manage and select Compact & Repair Database.)
      1. You may receive an error, this is fine.
    2. It doesn't appear that anything happens, but the file size will be considerably smaller.
  7. IMPORTANT!! Exit from Access before submitting the file! I may not be able to open your file otherwise. Submit on Laulima in Tasks Tests & Surveys

Important Note: Do not delete this file, it is needed for Software Assignment- Access Part B

Need Help?
Contact the instructor
via UH email
Please attach your file to the email.

If printing out this assignment. Please be aware that corrections or changes may be made to the assignment at any time. Please check back to the web version to check for changes. Also there may be links on this page that you need to access for the assignment - please go back to the web version to access the links.