PROJECT DESCRIPTION
Great Outdoors Tours is a small business that organizes day-long, guided trips of Massachusetts, Maine, New Hampshire, and Vermont. The company provides customers with a wide variety of hiking, biking, and paddling trips for all levels of experience. With a recent growth in business, Great Outdoors Tours has developed a database in Access 2013 to organize its records. The company would like your help with creating reports that highlight certain information.
GETTING STARTED
Download the following file from the SAM website:
SC_Access2013_C6_P1a_FirstLastName_1.accdb
Open the file you just downloaded and save it with the name:
SC_Access2013_C6_P1a_FirstLastName_2.accdb
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.
To complete this project, you will also need to download and save the following support file from the SAM website:
support_SC_A13_C6_P1a_States.txt
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
Import the data from the file support_SC_A13_C6_P1a_States.txt, available for download from the SAM website, and then append the records to the States table. The text file is a delimited file with a comma separating the two fields. Do not create a new table, and do not save the import steps.
Open the Basic Trips List report in Design View, and then complete the following tasks:
Add the current date and time to the Report Header section.
In the Date and Time dialog box, use the option button to select the second date format (e.g., 01-Aug-16) and the second time format (e.g., 4:18 PM).
Resize the report so that the right boundary of the report is approximately at the 8" mark on the horizontal ruler.
Save the report without closing it.
With the Basic Trips List report still open in Design View, complete the following tasks:
Select the State Header, and then display the Property Sheet for this section.
Change the Repeat Section property to Yes.
Change the Force New Page property to Before Section.
Save and close the report.
Modify the Trips by Guide Report report to match Figure 1 on the following page by completing the following tasks:
Open the Trips by Guide Report report in Design View.
Open the field list for the report.
Add the LastName field to the GuideID Header section of the report in the position shown in Figure 1. (Hint: You may need to reposition and resize the LastName control and/or its label after inserting it into the report.)
With the Trips by Guide Report report still open in Design View, complete the following tasks:
Select the TripID, TripName, and StartLocation controls simultaneously. Do not select the labels, only the controls.
Group these controls.
Resize the controls by dragging their right edge to the 3" mark on the ruler, as shown in Figure 1.
Bold these controls.
Save and close the Trips by Guide Report report.
Modify the Balance Due Report report to match Figure 2 on the following page by performing the following tasks:
Open the Balance Due Report report in Design View.
Change the Text6 label in the Page Header section to Due.
Format the text box control in the Detail section containing the =[TripCost]-[Deposit] calculation so that it displays with the Currency number format with zero decimal places, as shown in Figure 2.
Save and close the Balance Due Report report.
Figure 2: Balance Due Report in Report View
Open the Guide Phone List report in Design View, and then change the Alternate Back Color property for the Detail section to None. (Hint: The Alternate Back Color property for the Detail section will be No Color.) Save and close the report.
Open the Master Guide Report report in Design View and use the Subform/Subreport Wizard to add a subreport with the following options:
Add the subreport below the StartLocation label in the Detail section, at approximately the 1" mark on the vertical ruler.
Use the Reservations table for the subreport.
Select the ReservationID, TripID, ContactFirst, and ContactLast fields (in that order) from the Reservations table to add to the subreport.
Ensure that the Choose from a list. option button is selected, and accept the default link (Show Reservations for each record in Guides and Trips using TripID) for the subreport.
Save the subreport as Reservations subreport (which is the default name). (Hint: The width of the subreport will expand the width of the report to approximately 8". Do not manually adjust the width.)
Delete the label associated with the subreport.
Save the Master Guide Report report, switch to Report View, confirm your report matches Figure 3 below, and then close the report.
Figure 3: Master Guide Report in Report View
Open the Trips by Season Report report in Design View, and then update the report to match Figure 4 on the following page by completing the following tasks:
Resize the width of the report as shown in Figure 4. (Hint: The right border of the report is at the 7" mark on the horizontal ruler.)
Select the GroupSize, GuideID, Type, and Level controls simultaneously. Do not select the labels.
Align the controls to the right as shown in Figure 4.
Save and close the report.
Figure 4: Trips by Season Report in Design View
Open the Reservations Financial Report report in Design View, and then modify the report by completing the following tasks:
Select all labels in the Page Header section.
Bold the labels.
Resize the Detail section of the report by dragging the lower boundary of the section to just below the controls in the report, as shown in Figure 5 on the following page.
Save and close the report.
Figure 5: Reservations Financial Report in Design View
Modify the Guides Financial Report report to match Figure 6 on the following page by performing the following tasks:
Open the Guides Financial Report report in Design View.
Add a text box control in the GuideID Footer section in the position shown in Figure 6.
Convert the text box control into a calculated control that sums the TripCost field. (Hint: Your control should contain the formula =Sum([TripCost]).)
Format the control so that it displays with the Currency number format with zero decimal places.
Change the label to Guide Total as shown in Figure 6.
Reposition the control and its label to the approximate location show in Figure 6. (Hint: The left edge of the text box should be located at the 5" mark on the horizontal ruler.)
Save and close the report.
Figure 6: Guides Financial Report in Design View
Create a new blank report in Design View, and then complete the following tasks:
Open the Property Sheet for the report, and then set the Record Source for the report to the States table.
Save the report with the name State Report, but do not close the report.
Add the fields State and StateName to the report, and then reposition them to the approximate location shown in Figure 7 on the following page.
Add the title State Report to the Report Header section.
Add page numbers to the report at the Top of Page (Header) position, using the format Page N of M and the Right alignment. The page number should appear on the first page of the report.
Save and close the report.
Figure 7: State Report in Design View
Open the Trips by State Report report in Design View. Group the report by the State field, and then sort the report by the TripID field in ascending order. Do not add any additional grouping or sorting options to the report. Save and close the Trips by State Report report. (Hint: This report does not contain any fields.)
Modify the Reservations Total Report report to match Figure 8 on the following page by performing the following tasks:
Open the Reservations Total Report report in Design View.
Add a text box control to the Report Footer section in the position shown in Figure 8.
Convert the text box control into a calculated control that sums the TripCost field. (Hint: Your control should contain the formula =Sum([TripCost]).)
Format the control so that it displays with the Currency number format with zero decimal places.
Change the label to Total as shown in Figure 8.
Reposition the control and its label to the approximate location shown in Figure 8. (Hint: The left edge of the text box should be located at the 5" mark on the horizontal ruler.)
Save and close the report.
Figure 8: Reservations Total Report in Design View
Save and close any open 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.
