OBJECTIVES & PURPOSE:

The purpose of this assignment is to become familiar with Microsoft's Access 2000, Access XP, Access 2003, Access 2007 or Access 2010, a database management system, and its features including:

• Designing and using Access's Forms Generator and its various features;
• Designing and using Access's Report Generator and its various features;
• Designing and using Access's Query and its various features;

SPECIFICATIONS & INSTRUCTIONS:

PLEASE NOTE:

• To receive full credit, all specifications must be met.
• Read the NOTES at the bottom of the assignment before continuing;
• Learning how to do this assignment:
  • The following descriptions are the specifications for the assignment;
  • They are not the instructions on how to do the assignment;
  • Discussion and demonstration on how to do the assignment will be given in class on the demonstration day for this assignment (please attend class that day for the demonstration);
• Use only Microsoft's Access for this project. Other database products may not allow you to meet all of the requirements for the assignment.
• This assignment assumes the use of Microsoft's Access 2000, XP, or 2003. The steps documented below may not work on earlier versions of Access or other products. If ACCESS 2000, XP, or 2003 is not installed on the computer you use, schedule time to use a computer in an UNLV computer lab.
  • NOTE: If you use ACCESS 2007 or Acess 2010, the menu items and tools may be different. These instructions are written for ACCESS 2003.

Using Access 2000, XP, or 2003 and the Home Inventory List you created for the previous assignment (with Access 2000, XP, or 2003), create and demonstrate:

1. A "Form" for data entry & editing of Home Inventory records;
2. A "Report" that prints Home Inventory records;
3. A "Query" that selects records with five (5) selection criterion.

that meets the following specifications (all specifications must be met for full credit):

1. FORMS:
   Use Access 2000, XP, 2003's, or 2007's or 2010's Form Generator to create a Form for record entry and editing as follows:
   • After launching Access 2000, XP, 2003's, 2007's or 2010's, open the database that has your Home Inventory Table;
   • Using the Form Wizard:
     • Select your Home Inventory table;
     • Select all fields to appear in your form;
     • Use "Columnar" layout;
     • Use "International" or "Clouds" style;
       NOTE: If using Access 2007 or Access 2010, use a style with an observable background image, since "International" and "Clouds" Style may not be available.
     • Be sure to save your Form (when asked);
   • Display a record on your form;
   • Print a copy of your Form showing a record from the Home Inventory table as follows:
     • Click File-Print;
     • Set the "Print range" options to:
       • Click "Pages from:"
       • Specify from: 1 to: 1 (i.e., print from page 1 to page 1);
     • Click the "OK" button in the "Print" window.
       This procedure will print one page of record(s) in your Access Form.
   
   
2. REPORTS:
   Use Access's Report Generator to create a report as follows:
   • After launching Access, open the database that has your Home Inventory Table;
   • Using the Report Wizard:
     • Select your Home Inventory table;
     • Select all fields for the report;
     • Group records by the Room field;
     • Sort records by the Price field in descending order;
     • Include "Summary Options" of:
       • A sum of the PRICE field;
       • Show Detail and Summary;
       • Calculate percent of total sums;
     • Set "LAYOUT" to:
       • Blocked;
       • Portrait orientation;
       • Check the box to adjust field widths so all fields fit in on one page;
     • Set type face/style to "Corporate";
   • Make the following modifications to the report (which can be done through the "Design View" option of the report generator):
     • Title:
       • In all uppercase letters, modify the report Title to "NAME'S HOME INVENTORY", where "NAME'S" is your name (such as "JOHN'S HOME INVENTORY");
       • Center align the report Title to the middle of the page;
     • Room Footer:
       • Move the Summary Line (of record count) below the Sum and Percent lines;
       • Make sure the Sum and Percent summaries are lined (vertically) with the Price column;
       • Adjust the positions ot the Summary Line and the Sum and Percent lines to avoid unneccessary gaps in spaces.
     • Adjust line spacing, if neccessary, to so that the report is one page in length.
   • Preview the report. When it appears as specified above, print it.
   
   
3. QUERIES:
   Using Access's Query features, setup a Query for your database file (or table) to show only those home inventory items that meet the selection criteria shown below:
   • After launching Access, open the database that has your Home Inventory Table;
   • Using the Query Design View set the Selection Criteria as:
     • Items in the "DEN
       and
       • Were aquired
         • On or after January 1, 2003
           and
         • on or before December 31, 2003
         and
       • Cost
         • At least $100
           and
         • Less than $1000
   • Shown below is how the Query Design View screen should appear after the entries are made:
     
     

     Home Inventory Query

     Field Name	ITEM	ROOM	DATE	PRICE
     Sort ?				descending
     Show Field?	Check (Yes)	Check (Yes)	Check (Yes)	Check (Yes)
     Select Criteria		="DEN"	>= 01/01/03 AND <= 12/31/03	>= 100 AND < 1000
     Logical Connecting Operator		AND	AND
     Explainations
     		Room is equal to "DEN"	Date is greater than or equal to January 1, 2003 AND Date is less than or equal to December 31, 2003	Sort in Descending order Price is greater than or equal to $100 AND Price is less than $1000
     		AND	AND

     
     
   • Include in the query a sort that sorts in descending* order by price the querried list of matches;
   • Apply (execute) the query to the file (table), producing the list of selected records that meet the above specifications.

ASSIGNMENT SUBMISSION:

Submit as your assignment the following printouts stapled together in the following order:

1. Your FORMS hardcopy printed in a step above;
   Hand write in the upper right corner of this hardcopy your:
   • Grade Sheet Identification (Section#-Row#);
   • Name;
   • Student ID#;
   • Section#;
   • Assignment#.
2. Your REPORT hardcopy printout in a step above;
3. Your QUERY hardcopy printout.

NOTES:

• To receive full credit, all specifications must be met.
• This assignment assumes the use of Microsoft's Access 2000, XP, or 2003. The steps documented may not work on earlier versions of Access or other products.
• If ACCESS 2000, XP, or 2003 is not installed on the computer you use, schedule time to use a computer in an UNLV computer lab.
  • NOTE: If you use ACCESS 2007 or Access 2010, the menu items and tools may be different. These instructions are written for ACCESS 2003.

GRADING:

The instructor and/or Grader will be verifying that all specifications stated above in SPECIFICATIONS AND INSTRUCTIONS as well as ASSIGNMENT SUBMISSION were met to receive full credit. Specifications missing will be the basis of deducted points. Items the instructor or grader examine include (but are not limited to):

• DOCUMENT:
  • Page Setup:
  • Page settings:
  • Portrait orientation;
  • Letter size paper (8.5 x 11 inches).
  • Database Table:
    
    • Using Access's "Design View", the record structure (Data Dictionary, or Schema) must be defined as specified in the previous assignment, which is:
      
      

      Field Name	Field Type	Size (Characters)
      ITEM	Text	15
      BRAND	Text	15
      MODEL	Text	15
      SERIAL	Text	20
      DATE	Date	(Access default)
      PRICE	Currency	(Access default)
      ROOM	Text	15

    
    
    • No field should be a "Primary Key";
    • Must have at least 15 records:
    • With at least 4 records showing the same room."
• PRINTOUTS (all in portrait orientation, and collated in the following order):
1. Forms Printout:
• Show only one page of Form(s);
• All fields in a record must show (no primary key field);
• Forms layout is "Columnar";
• Forms style (background) is "International" or "Clouds";
2. Report Printout:
• Title of report's type face/style is set to "Corporate"
• Title is centered on top line of the page;
• All fields show for each record on one line;
• Records are grouped by Room;
• Records in each group are sorted by Price in descending order;
• Report "LAYOUT" is:
• Blocked;
• Portrait orientation;
• Group (Room) Footer appears at the end of each room grouping, showing in this order:
• The sum of the PRICE field for the room;
• The calculated percent of total sums for the room;
• The summary of detail (number of records) for the room;
3. Query Printout showing:
• Only records that are:
• In the Room called "DEN";

AND
• Purchased on or after January 1, 2003
  AND
  Purchased on or before December 31, 2003
  AND
• Cost at least $100
  AND
  Cost less than $1000
• Records in decending sorted order by Price;