| Illustrated Access 2013 Units E–H: SAM Capstone Project 1a Musical Instrument Database Enhancing tables, queries, forms, and reports
|
PROJECT DESCRIPTION
You work for a music store that rents 40 different musical instruments to students from six different local schools. The store uses a database to track information about the instruments, students, rentals, and schools. You'll enhance the database by updating tables, queries, forms, and reports.
GETTING STARTED
· Download the following file from the SAM website:
o IL_Access2013_CS_UE-H_P1a_FirstLastName_1.accdb
· Open the file you just downloaded and save it with the name:
o IL_Access2013_CS_UE-H_P1a_FirstLastName_2.accdb
o Hint: If you do not see the .accdb file extension in the Save file dialog box, do not type it. Access will add the file extension for you automatically.
· Open the _GradingInfoTable table and ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.
PROJECT STEPS
1. View the relationships for the database and create a report. Save the report with the name RelationshipReport, then close it. Close the Relationships window.
2. Open the Customers table in Design View. Use the Input Mask Wizard to add a Phone Number input mask to the Phone field. Use the default Input Mask options with one exception: Change the Placeholder character to an asterisk (*). (Hint: Make sure that your data is stored without symbols in the mask.) Save but do not close the Customers table.
3. With the Customers table still open in Design View, specify the City field as a required field. Save but do not close the Customers table, then click Yes when prompted to test the existing data.
4. With the Customers table still open in Design View, add a new field after the Phone field with the name Birthdate. Specify the Date/Time data type and the Medium Date (e.g., 12-Nov-17) format property for this field. Save and close the Customers table.
5. Open the Instruments table in Design View and update the MonthlyFee field properties as described below:
a. Create a Validation Rule that specifies that all entries in the MonthlyFee field must be greater than or equal to 50.
b. Set the Validation Text for this field to The minimum monthly rental fee is $50. (including the period).
Save and close the Instruments table, then click Yes when prompted to test the existing data.
6. Open the Rentals table in Design View. Below the RentalDate field, add a new field to the table named Salesperson. Use the Lookup Wizard to set the Lookup properties for the Salesperson field as described below:
a. Select the option to type in the values for the lookup field.
b. Enter Brown, Carpenter, and Lui as the three possible values for the lookup field.
c. Use the default label Salesperson for the field label.
d. Limit entries to the list and do not allow multiple values.
Save the Rentals table, but do not close it.
7. Switch to viewing the Rentals table in Datasheet View. Update the RentalPeriod values for the following records:
a. For the record with a RentalNo field value of 1, update the RentalPeriod field value to Quarter.
b. For the record with a RentalNo field value of 20, update the RentalPeriod field value to Academic Year.
c. For the record with a RentalNo field value of 37, update the RentalPeriod field value to Semester.
Close the Rentals table, saving it if prompted to do so.
8. Create a new query in Design View based on the Customers table with the following options:
a. Add the City, LastName, and FirstName fields (in that order) to the query.
b. Add the OR criterion to the City field, so that only records with a City field value of Ankeny or Clive are returned by the query.
c. Add an ascending sort order to the City, LastName, and FirstName fields.
d. Save the query with the name AnkenyCliveStudents. (Hint: Do not include the period.)
Run the query, view it in Datasheet View, then close the query. (Hint: If you entered the criteria correctly, the query should return six records.)
9. Create a new query in Design View based on the Customers, Rentals, and Instruments tables with the following options:
a. Add the LastName and City fields from the Customers table, add the RentalDate field from the Rentals table, and add the Description and MonthlyFee fields from the Instruments table (in that order) to the query.
b. Add the AND criterion to the query, so that only records with the value Clarinet in the Description field and West Des Moines in the City field are returned by the query.
c. Add an ascending sort order to the LastName field.
d. Save the query with the name ClarinetWestDesMoines.
Run the query, view it in Datasheet View, then close the query. (Hint: If you entered your criteria correctly, the query should return one record.)
10. Open the InstrumentRevenue query in Datasheet View. Add a Total Row to the query that totals the values in the MonthlyFee field using the SUM function. Save and close the query.
11. Open the RentalCrosstab query in Design View. Turn the RentalCrosstab query into a Crosstab query as described below:
a. Use the Crosstab button to add the Total row and Crosstab row to the query.
b. Select the Description field as the row heading field.
c. Select the SchoolName field as the column heading field.
d. Sum the value of the MonthlyFee field.
Save and run the RentalCrosstab query. Compare the query to Figure 1 on the following page, then close the query. (Hint: You will apply a theme to the database in Step 22, so the fonts in your query may look different from the fonts shown in Figure 1.)
Figure 1: RentalCrosstab Query in Datasheet View
| Copyright © 2014 Cengage Learning. All Rights Reserved. |
12. Open the RentalsBySchool query in Design View and complete the following steps:
a. After the MonthlyFee field, add the SerialNo field from the Rentals table to the query.
b. Add the Total row to the query grid.
c. Using the Total row, group the records by the SchoolName field. (Hint: This setting is automatically applied to all fields when the Total row is added to the query grid.)
d. Using the Total row, sum the values of the MonthlyFee field.
e. Using the Total row, count the number of records for each group using the SerialNo field.
Save and run the RentalsBySchool query. Compare the query to Figure 2 on the following page, then close the query. (Hint: You will apply a theme to the database in Step 22, so the fonts in your query may look different from the fonts shown in Figure 2.)
Figure 2: RentalsBySchool Query in Datasheet View
| Copyright © 2014 Cengage Learning. All Rights Reserved. |
13. Open the CustomerEntry form in Design View and complete the following:
a. Set the Record Source property of the form to the Customers table. Save but do not close the form.
b. Add the Phone field just below the Zip field in the approximate location shown in Figure 3 on the following page. (Hint: You will apply a theme to the database in Step 22, so the fonts in your query may look different from the fonts shown in Figure 3.)
c. Select the Zip and Phone labels (but not the controls) and align them using the Right option.
Save and view the CustomerEntry form in Form View, then close the form.
Figure 3: CustomerEntry Form in Design View
| Copyright © 2014 Cengage Learning. All Rights Reserved. |
14. Open the CustomerInfo form in Design View. Make sure that the Control Wizards feature is turned on. Using the Subform Wizard, add a subform to the form as described below:
a. The subform should be located in the approximate location shown in Figure 4 on the following page.
b. Use an existing table or query as the data for your table.
c. From the Rentals table, add the RentalNo, CustNo, SerialNo, and RentalDate fields (in that order) to the subform.
d. Show records in the Rentals table for each record in the Customers table using the common CustNo field.
e. Name the subform Rentals.
Save and view the CustomerInfo form in Form View, then compare it to Figure 4. (Hint: You will apply a theme to the database in Step 22, so the fonts in your query may look different from the fonts shown in Figure 4.) Close the CustomerInfo form.
Figure 4: CustomerInfo Form in Design View
| Copyright © 2014 Cengage Learning. All Rights Reserved. |
15. Open the InstrumentEntry form in Design View, then delete the control containing the date expression from the Form Header section. Save the InstrumentEntry form, but do not close it.
16. With the InstrumentEntry form still open in Design View, complete the following actions:
a. Select the Description, SerialNo, and MonthlyFee controls (but not the labels), then align the controls using the Left option.
b. Select the Monthly Fee label and the MonthlyFee control, then align them using the Top option.
Save but do not close the InstrumentEntry form.
17. With the InstrumentEntry form still open in Design View, confirm that the Control Wizards feature is turned on. Using the Combo Box Wizard, add a combo box to the form as described below:
a. Add the combo box to the Form Header section at approximately the 3.5" mark on the horizontal ruler.
b. Format the combo box to find a record in the form.
c. Select the Description and SerialNo fields (in that order) to display in the Combo Box.
d. Do not hide the key column.
e. Enter Find Instrument as the label for the combo box.
Save but do not close the InstrumentEntry form.
18. With the InstrumentEntry form still open in Design View, add a command button to the form as described below:
a. The command button should be located in the approximate location shown in Figure 5 below.
b. The command button should use a Form Operation to close the form.
c. The command button should display the text Close Form.
d. Use CloseForm as the meaningful name for the command button.
e. If necessary, resize and reposition the command button.
Save the InstrumentEntry form and compare it to Figure 5. Close the form and save it if prompted to do so.
Figure 5: InstrumentEntry Form in Design View
| Copyright © 2014 Cengage Learning. All Rights Reserved. |
19. Open the Navigation form in Design View. Add a tab control to the form at approximately the 0.5" mark on both the horizontal and vertical rulers. Complete the following steps:
a. Rename the first tab Forms.
b. Rename the second tab Reports.
Save and close the form.
20. Open the SchoolListing report in Design View. Open the property sheet for the report, then set its Record Source property to the Schools table. Save but do not close the SchoolListing report.
21. With the SchoolListing report still open in Design View, open the Field List and complete the following steps:
a. Add the SchoolContact field to the Detail section of the report at approximately the 4" mark on the horizontal ruler (as shown in Figure 6 below).
b. Cut the label associated with the School Contact control and paste it into the Page Header section at approximately the 4" mark on the horizontal ruler.
Compare your report to Figure 6, then save but do not close the report.
22. With the SchoolListing report still open in Design View, apply the Facet theme to the database. Save the report, then compare it to Figure 6. Close the SchoolListing report.
| Copyright © 2014 Cengage Learning. All Rights Reserved. |
23. Open the InstrumentListing report in Design View, then apply a conditional formatting rule to the MonthlyFee control. The rule should highlight any record with a MonthlyFee field value greater than 60 using bold text and a yellow background. Save but do not close the InstrumentListing report.
24. With the InstrumentListing report still open in Design View, use the Format Painter to copy the formatting from the Description label in the Page Header section to the SerialNo and MonthlyFee labels in the Page Header section. Save but do not close the InstrumentListing report.
25. With the InstrumentListing report still open in Design View, draw a straight line that spans the report's width across the bottom of the Description Footer section, as shown in Figure 7 below. Save but do not close the InstrumentListing report.
26. With the InstrumentListing report still open in Design View, modify the properties of the report sections as described below:
a. Close (but do not remove) the Description Header section.
b. For the Description Footer section, update the Force New Page property to force a new page after each section using the After Section option.
Save and preview the InstrumentListing report, compare it to Figure 7, then close it.
| Copyright © 2014 Cengage Learning. All Rights Reserved. |
27. Open the CustomerList report in Design View and remove the Report Header and Report Footer sections. Save and close the CustomerList report.
28. Open the RentalRevenue report in Design View and confirm that the Control Wizards feature is turned on. Using the Subreport Wizard, add a subreport to the report as described below:
a. Add the subreport to the Report Footer section at approximately the 0.5" mark on the horizontal ruler and the 0.5" mark on the vertical ruler.
b. Use an existing report or form and select the CustomerList report for the subreport.
c. Select the None option at the bottom of the suggested link list as the link between the main report and the subreport.
d. Name the subreport Students.
Save the RentalRevenue report, then compare it to Figure 8 below. Close the report and save it if prompted to do so.
| Copyright © 2014 Cengage Learning. All Rights Reserved. |
Save and close any open database objects in your database. Compact and repair your database, close it, and exit Access. Follow the directions on the SAM website to submit your completed project.
NOTE: Step 22 and 26a not done
