OBJECTIVES & PURPOSE:

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

• Designing the schema (data dictionary, or record layout) of a file using a database management system (i.e., Access);
• Entering and editing records;
• Displaying records on the screens;
• Sorting records in a database;
• Filtering (selecting) records in a database;
• Printing records.

SPECIFICATIONS & INSTRUCTIONS:

PLEASE NOTE:

• To receive full credit, all specifications must be meet.
• 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 or XP or 2003. The steps documented below may not work on earlier versions of Access or other products. If ACCESS 2000 or 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, the menu items and tools may be different. These instructions are written for ACCESS 2003.

Using Access (a spreadsheet or word processor cannot be used for this project) prepare a database that records, stores, retrieves, displays, sorts, filters, and prints a home inventory list as per the following specifications (all specifications must be met for full credit):

1. Set Page Orientation to landscape in "Page Setup" (File > Page Setup);
2. Create a New "Blank Database";
   • Be sure to save the new database on your Flash Drive or on your Home Directory.
     
   • Beware! If you do not specify a device for saving your new database, Access will save the database in "My Documents". Remember, when using a PC on campus, anything stored in "My Documents" on that PC will be erased when you log out off that PC!
3. Create a database file (or table) with fields for the information on household items as shown in the Table below:
   • Use the "Design View" feature to create the Database Table;
   • Do not use the "Wizard" to create the Database Table because it will not give you control to set the field names, types and sizes in the record schema layout. You may not use the "Wizard" and then define the field names by creating a record in which the contents are the field names;
   
   
   

   Data Dictionary (Record Schema) For Home Inventory

   Field Name	Field Type	Size (Characters)	Comment
   ITEM	Text	15	Name of household item
   BRAND	Text	15	Brand of household item
   MODEL	Text	15	Model of household item
   SERIAL	Text	20	Serial number of household item
   DATE	Date	(Access default)	Date item was purchase
   PRICE	Currency	(Access default)	Price of household item
   ROOM	Text	15	Room household item is in

   
   The Purchase Price field must be formatted with commas and two places to the right of the decimal, or as "currency".
   
   The field sizes labeled "Access default" will be set by Access.
   
   When the Data Dictionary (Record Schema) has been defined:
   • Close the Data Dictionary Window;
   • Save the Table as a name like "Home Inventory";
   • Answer "No" to creating a Primary Key Field; it is an not needed for this project;
   
   
4. Enter at least 15 records:
   • in at lease 4 different rooms
   • with least 4 records of items in the same room.
   • Enter 5 records in which the DATE purchased is between January 1, 2003 and December 31,2003,
     and in which:
     • Three (3) of these 5 records must have their PRICE between $100 and $999, and in which 2 of these 3 records are in the room called "DEN";
     • One (1) of these 5 records must have its PRICE less than $99
     • One (1) of these 5 records must have its PRICE greater than $1000
       
       NOTE: These 5 records are needed for the following assignment;
       
       
   • Enter records in a random order (not in order by room or by price or by date).
   • Do not enter a record whose fields are the field names (i.e., Item, Name, Model#, Serial#, ..., etc). When using Access properly, the field name will automatically be included in a heading of a list (table) style printout.

ASSIGNMENT SUBMISSION:

Follow the procedures herebelow and turn in the three requested printouts:

1. Print a hard copy (in landscape orientation*) of all records in table (list) style in the order they were entered by:
   • Clicking the "Print" tool button
                       OR
     Clicking File > Print menu options.
     
     *How to set Landscape Orientation:
     • If using Access 2003, click File > Page Setup > Landscape Orientation.
     • If using Access 2007, click Office Button > Print > Print Preview > Landscape (in "Page Layout" options).
     
     
     
2. Sort all records in descending order by purchased price* by:
   • Clicking the PRICE field column header (to select the PRICE field for sorting);
   • Clicking the "Sort Descending" tool button
                       OR
     Clicking Records > Sort > Sort Descending menu options.
3. Print a hard copy (in landscape orientation) in table (list) style of all records in descending order by Purchase Price by:
   • Clicking the "Print" tool button
                       OR
     Clicking File > Print menu options.
4. Filter only those records from the same room with four or more items entered in Step 3 of SPECIFICATIONS & INSTRUCTIONS by:
   • Clicking the "Filter By Forms" tool button;
                       OR
     Clicking Report > Filter By Forms menu options.
   • Click a cell in the ROOM field column;
   • Select the room with four or more records;
   • Click the "Apply Filter" tool button.
   Filtered records are to be in descending sorted order by Purchase Price. *
5. Print a hard copy (in landscape orientation) in table (list) style of only the filtered (selected) records by:
   • Clicking the "Print" tool button
                       OR
     Clicking File > Print menu options.
6. Hand write in the upper right corner of each page your:
   • Name;
   • ID#;
   • Section#;
   • Assignment#.
   • Staple all three printouts together in order and submit them as your assignment.

NOTES:

• To receive full credit, all specifications must be met.
• This project is a database management system project. Credit for this assignment will not be given if a spreadsheet or word processor is used.
• This assignment assumes the use of Microsoft's Access 2000 or XP or 2003. The steps documented may not work on earlier versions of Access or other products.
• If ACCESS 2000 or 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.

* Descending sorted order by purchase price means that records are in sorted order with the highest priced item first and the lowest priced item last.

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:
  • Landscape 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:
      
      

      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 landscape orientation):
1. First Printout (of Table) showing:
• At least 15 records entered in random order;
• With fields as defined above;
• No Primary Key;
2. Second Printout (of Sorted Table) showing:
• All records in decending sorted order by Price;
3. Third Printout (of Filtered/Sorted Table) showing:
• At least 4 records, all from the same room;
• Records in decending sorted order by Price;