iLAB OVERVIEW

Scenario/Summary

Hopefully you will find this week’s lab activity fun and useful. If you have never created a database, you will be able to tell all of your friends and family that you are now a database designer! You will walk through the steps of creating and populating an Access database to keep a record of items in your home. The database will have a table, a form, a query, and a report. You will need to use Microsoft Access 2010. In the event you do not have Access, you can use the iLab environment. Make sure to copy your files down to your pc and upload to the Dropbox. The following videos will refresh you with the use of the iLab environment and transfer of files.

Deliverables

Turn in one database named Lab7_yourlastname.accdb to the Dropbox. When submitting the database, provide a comment in the Dropbox comments area explaining what you learned from completing this lab activity.

iLAB STEPS

STEP 1: Create a Table (10 points)

.NOTE:For this lab, you will not have a starter file. You will create a database from scratch.

In this step, you will create a blank database and add one table to the database. The table should contain at least the following fields:

• ID: Autonumber field to provide a unique identifier for each item in your table
• Item: Text field to store the Item name
• Description: Memo field to store a description of the item
• Condition: Lookup field to store the condition the item is in (Good, great, etc.)
• Acquired Date: Date field stored in “Short Date” format (M/DD/YYYY)
• Purchase Price: Currency Field to store the price you paid for the item
• Location: Lookup field to store the names of the rooms in your house
• Manufacturer: Text field
• Model: Text field to store information about the model of the item
• Comments: Memo field to store any comments you might wish to add
• SerialNumber: Text field to store the serial number of the item (where applicable)
• Warranty: Boolean field (Yes/No) that will record whether the item has a warranty
• ManufacturerURL: Hyperlink field with the support website for the item (optional)

You may add other fields if you wish.

Entering data in Datasheet View requires a large amount of scrolling. It also exposes the table design to users, which means that users can make changes to the database design that you don’t wish them to make. Create a form to enable easy entry of data. Use the form to enter at least 10 records.

1. Create the form. Select an AutoFormat and modify the form to make it more user friendly by adding a descriptive title and making minor modifications to the form layout.
2. Enter 10 records. For at least one of these records, select Office as the Location.

Lab 7, Step 2 VideoThis video walks through the current step(s) in this week’s Lab. The video may provide alternative ways of completing steps and may not cover all required steps. It is provided as a tool to help you, but please ensure that you have completed all steps as described in the instructions and Step-by-Step guides. ** Please be patient when loading these videos as they can be large.

STEP 3: Create a Query to Select Records (10 points)

Back to TopDownload theLab 7, Step 3 Instructions.

Once data have been entered into a database, you will want to be able to select and retrieve data. The Query object allows you to select data from your database tables that will answer questions you might have. For example, suppose you had a small fire in your office and you wish to make an insurance claim. Using a query, you will be able to select only those records in the database that have the Location equal to “Office.” Write a query that will allow you to select records based on a room you select.

1. Use the Create Tab and select Query Wizard. Create a Simple query that selects Item, manufacturer, Model, Serial Number, Purchase Price, Acquired Date, and Location.
2. In Design View, add the Condition field to the query. Add a Selection Criteria to the Location field so that only those records with the Location equal to Office are displayed.
3. Save the query as qryItemsbyRoom.

STEP 4: Create a Report Showing All Items Grouped by Room (15 points)

Reports allow us to create “printable” documents based on the data in our database. Create a report of your entire Inventory, grouped by room. Show only the Item, Manufacturer, Model, and Purchase Price.

1. Use the Report Wizard to create the report.
2. Modify the design so that the title is “Inventory by Room Report” and all data and labels are sized appropriately.
3. Add totals by room and a grand total to the report. Add Labels to the totals.