MIS 204 Hands-On Activity:
Access - Forms and Reports
Introduction
In this assignment you will learn how to create forms and reports to access and analyze your data. Forms are useful for data entry and navigation. Reports allow you to connect data and create information and then distribute that information as a document.
Step 1: Open Your Lesson 8 HOA Database
Open the database file you created for the Access Intro Hands-On Assignment Then, click File?Save As and give the file a new name using the format of lastname + first initial +HOA Forms-Reports (e.g., smithaHOA Forms-Reports).
Step 2: Modify the BOOKS table.
1. Open the BOOKS table in design view.
2. Add PRICE as a field after the GENRE field.
3. The data type should be Currency.
4. Save the BOOKS table.
5. Switch to the Datasheet view. You will now have a PRICE column that is empty. You need to populate it with price data, which is listed in the table below. You do not need to enter dollar signs!
Book Price
Hunger Games $5.85
Dune $12.24
EXCEL for Dummies $18.20
Emma $3.50
Harry Potter $10.19
Game of Thrones $9.99
A Dance with Dragons $21.00
Catch---22 $10.88
Steve Jobs $21.00
When done your BOOKS table should look like this:
Figure 1 - BOOKS Table After Entering PRICE Data
6. Close your BOOKS table.
Step 3: Create a Data Entry for Adding New Books to the Inventory
1. Click on the Create tab.
2. Click on Form Wizard.
3. Make sure the BOOKS table is selected and add all fields to the Selected Fields box.
4. Click Next.
5. When it asks "what layout would you like for your form?" just leave "Columnar" selected
6. Click Next.
7. Change the form title to BOOK ENTRY FORM.
8. Click Finish.
Your form should look like this:
If you look at the bottom of your form you will see a navigation bar that displays what record you are viewing, if anyfilter is applied, and a search field. You can use this bar to navigate through the existing records in the table, search for a record, and add a new record. You can even change the data in the table!
9. Navigate to the record for A Dance with Dragons and change the price to $19.99 and press ENTER.
10. Close your form.
11. Open your BOOKS table. You will see that the price has been updated!
Step 4: Create an Inventory Report
1. Click on the Create tab.
2. Click on Report Wizard.
3. Make sure the BOOKS table is selected and add the following fields: GENRE, AUTHORLAST, AUTHORFIRST, TITLE, and ISBN. It MATTERS what order you move these fields to the selected fields box.
4. Click Next.
5. Select and move GENRE when report wizard asks you if you want to add any grouping levels; you should seeGENRE text in blue above the report template.
6. Click Next.
7. Select AUTHORLAST as your first level of sorting. Select Ascending
8. Click Next.
9. Change the orientation to Landscape. Leave Layout as Stepped.
10. Click Next.
11. Change the title to BOOKS INVENTORY REPORT.
12. Click Finish.Your report should look something like this:
Figure 3 -BOOKS INVENTORY REPORT
13. Make sure that all the text in each row and column is visible.
14. Close Print Preview by clicking on the red ‘X' on the ribbon. You will now be in design view.
15. Save your form.
Step 5: Improve the appearance of the inventory report.
1. Within In the Design View for the BOOKS INVENTORY REPORT, click once on AUTHORLAST field within the PAGE HEADER section to select this field. The first click will select this field. Then click on it again, this should select the text in the field and allow you to change the text. Change it to Last Name.
Remaining in the Page Header section, make the following changes to these other fields:
a. GENRE to Genre.
b. AUTHORFIRST to First Name.
c. TITLE to Title
Figure 4 - The Form Page Header After Changes
2. Remaining in the Page Header section select each field individually by double clicking on each field.
When the Property Sheet window on the right opens up for each field, change the Font Weight from Normal tobold AND change the Font Color to Text Black from Text 1, Lighter 50%. Depending upon the version of Access, you may have other options for color. Close the Property Sheet.
3. Double click anywhere in the GENRE Header area (but not on the GENRE field itself).
In the Property Sheet change the Back Color from Background 1 to Text 2, Lighter 80%. To do this, you have to choose "Text Description" in the drop down menu of the Back Color property. Once you have chosen Text Description, you can over-write Text Description with Text 2, Dark Blue, Lighter 80%.
Change Alternate Back Color to Text 2, Lighter 80% as well. Depending upon the version of Access, you may have other options for color. Close the Property Sheet.
4. Double Click on the GENRE field in the GENRE Header area.
In the Property Sheet, Change the Back Color to Text 2, Lighter 80%. Change the Font Size to 14. Depending upon the version of Access, you may have other options for color. Close the Property Sheet.
5. Click on the field GENRE and then move the mouse cursor over the right border of the field in the GENRE Header section until the cursor changes to a line. Click and drag the border until it reaches the third vertical line from the left.
Figure 5 - The GENRE Field Resized
Move the mouse until the cursor hovers over the top edge of the Detail bar. The cursor should change to a horizontal bar bisected by a vertical line. Click and drag the bar down until the GENRE Header area is roughly twice as tall.
Make the GENRE field in the GENRE Header area three rows of dots larger by repeating the process above, only this time on the bottom border.
Figure 6 - The GENRE Field Resized to be Taller
6. Double click anywhere in the Detail area, but not on any of the fields.
In the Property Sheet, change the Alternate Back Color in the Property Sheet to Background 1. Close theProperty Sheet
7. Save your report and then switch to Report View. Your report should look similar to this:
Figure 7 - The Formatted BOOKS INVENTORY REPORT
Step 5: Submit Your Assignment
You have now completed your assignment. Upload your ACCESS database to the Hands-On Assignment Drop Box. Your assignment will be graded using the following rubric:
Category Points
Table modified correctly
• PRICE field added correctly
• PRICE data added to table correctly 20
BOOK ENTRY FORM created successfully
• Form created correctly (contains correct fields, shows data, etc.) 20
BOOKS INVENTORY REPORT created successfully
• All fields selected correctly
• Report formatted correctly 60
Total: 100 pts