Homework answers / question archive / Shelly Cashman Access 2019 | Module 4: SAM Project 1b City Tours Ltd

Shelly Cashman Access 2019 | Module 4: SAM Project 1b City Tours Ltd

MS Access

Share With

---

Shelly Cashman Access 2019 | Module 4: SAM Project 1b

City Tours Ltd.

CREATING REPORTS AND FORMS

GETTING STARTED

Open the file SC_AC19_4b_FirstLastName_1.accdb, available for download from the

SAM website.

Save the file as SC_AC19_4b_FirstLastName_2.accdb by changing the “1” to a “2”.

If you do not see the .accdb file extension in the Save As dialog box, do not type

it. The program 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.

City Tours Ltd. is a company that provides tours in a city that attracts tourists from

around the country. As a customer service manager for the company, you need to be

able to prepare reports for other managers and create forms that help in making

database updates.

Open the NewEngland Customer Report in Layout View. Group the report by the State

field, and then sort the report by the CustomerID field in ascending order. Do not add

any additional grouping or sorting options to the report. Close the Group, Sort, and Total

pane, then save and close the report.

2.

Open the All Tours Report in Layout View, and modify the report as follows:

a.

Sum the values in the AdultCost column and average the values in the ChildCost

column.

b.

Switch to Print Preview to view the report and to check that the values in the

subtotal controls and the total controls are not truncated (cut off) vertically.

c.

Return to Layout View, and, if necessary, drag the sizing handles for the subtotal

control and the total control down so that the values display completely.

Save the report without closing it.

3.

With the All Tours Report still open in Layout View, apply conditional formatting to the

NumAdult column. If the number of adults is greater than 2, display the value in bold,

Maroon font (6th column, 1st row of the Standard Colors palette.) Save the report

again and switch to Report view, confirm that it matches Figure 1, and then close it.

Open the DrivingTours Report in Layout View, and then create a summary report. Save

and close the report.

5.

Open the Home Tour Report in Layout View. Apply the Office theme to this object only,

then save and close it.

6.

Use the Label Wizard to create labels for the Customers table, and include the following

options:

a.

Use Avery C2163 as the label size.

b.

Use Arial font, 11 point font size, Light font weight, and Black (1st column, 6th

row of Basic Colors palette) font color with no special font styles for the labels.

(Hint: These formatting options may be the default settings for your label.)

c.

On the first line of the label, include the FirstName field, a space, and the

LastName field.

d.

On the second line of the label, include the State field.

e.

On the third line of the label, include the Mobile field. Your label should match

Figure 2.

f.

Sort the labels by the State field.

g.

Save the report as Labels Customers (which is the default name).

Preview the report in Print preview, confirm that it matches Figure 3, and then

close it (Hint: If a message appears warning that some data may not be displayed,

click OK.)

Figure 2: Prototype Customers Label

Figure 3: Labels Customers Report

7.

Use the Report Wizard to create a report based on the Customers and Confirmations

tables. The report should include the following options:

a.

Include the CustomerID and LastName fields from the Customers table.

b.

Include the ConfirmationID and TourNumber from the Confirmations table.

c.

The data will automatically be grouped by the CustomerID and LastName fields,

 

but do not add any additional grouping levels.

d.

Sort the report by the ConfirmationID field in ascending order.

e.

Use the Stepped layout and Portrait orientation.

f.

Save the report using Customers and Confirmations Report as the report

name.

Preview the report, confirm that it matches Figure 4, and then close it.

Figure 4: Customers and Confirmations Report

8.

Open the Guide StartDate Form in Layout View, and then modify the form by performing

the following tasks:

a.

Select all labels and controls in the Detail section of the form. (Hint: Do not select

the form title label in the Form Header section.) Place the selected controls in a

Stacked control layout.

b.

Add the StartDate control to the end of the form after the HourlyRate control as

shown in Figure 5.

c.

Move the LastName control after the FirstName control as shown in Figure 5.

(Hint: Be sure to select both the label and the control for the LastName field.)

Preview the form, confirm that it matches Figure 5, and then save and close it.

Figure 5: Guide StartDate Form

9.

Open the Guides Update Form in Layout View, and then bold the HourlyRate text box

control. Save and close the form.

10. Open the Tours Update Form in Layout View, and then add the current date to the form.

Use the option button for the second date format (for example, 12-Feb-20). Do not

include the time on the form. Save and close the form.

11. Use the Form Wizard to create a form based on the Confirmations table with the

following options:

a.

Include the ConfirmationID, CustomerID, TourDate, and TourNumber fields (in that

order) on the form.

b.

Select Columnar layout for the form.

c.

Save the form using Tour Date Confirmations Form as the form name.

Close the form.

Save and close any open objects in your database. Compact and repair your database, close it,

and then exit Access. Follow the directions on the SAM website to submit your completed project.