Shelly Cashman Access 2016 | Module 5: SAM Project 1a
West Coast Outdoor Advertisers
MULTIPLE-TABLE FORMS
GETTING STARTED
•
Open the file SC_AC16_5a_FirstLastName_1.accdb, available for download
from the SAM website.
•
Save the file as SC_AC16_5a_FirstLastName_2.accdb by changing the “1”
to a “2”.
o
•
If you do not see the .accdb file extension in the Save As 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.
Open the Advertiser table in Design View. Use the Input Mask Wizard to create
an input mask for the Phone field. Accept the default input mask for the Phone
Number field. Store the data without the symbols in the mask. Save the change
to the Advertiser table without closing it.
2.
Switch the Advertiser table to Datasheet View, and then add a new field named
Customer Notes at the end of the table. Use Long Text as the data type for
the field. Save the change to the table.
3.
With the Advertiser table still open in Datasheet View, update the record for
Boise Medical Services (which has the AdvertiserID BMS53) as described below:
a.
Using the input mask, enter 603-555-2883 in the Phone field.
b.
Enter the text Designs own ads in the Customer Notes field.
Close the table.
4.
Open the Rentals table in Design View, and then add a new field with the name
Rental Contract following the Discount field. Use Attachment as the data
type for the field, and use Rental Contract as the caption property of the field.
Save the change to the table, and then close the table.
5.
Modify the AdRep Data Entry Form to match Figure 1 on the following page by
completing these tasks:
a.
Open the AdRep Data Entry Form in Design View.
b.
Open the field list for the form.
c.
Add the PhoneNumber field to the form in the location shown in Figure 1.
Save and close the AdRep Data Entry Form.
Shelly Cashman Access 2016 | Module 5: SAM Project 1a
Figure 1: AdRep Data Entry Form – Design View
6.
Select the States table, and then create a new form in Design View. Update the
form to match Figure 2 on the following page by completing these tasks:
a.
Add the State and StateName fields (from the States table) to the Detail
section of the form, in the approximate location shown in Figure 2. If
necessary, reposition the controls and their labels after adding them to
the form.
b.
Save the form with the name State Data Entry Form, but do not close
the form.
c.
Add a title to the form. The title of the form should match the name of the
form.
Save and close the State Data Entry Form.
Shelly Cashman Access 2016 | Module 5: SAM Project 1a
Figure 2: State Data Entry Form – Design View
7.
Open the AdRep Master Form in Design View, and then ensure that the Use
Control Wizards button is selected.
With the AdRep Master Form still open in Design View, use the
Subform/Subreport Wizard to add a subform with the following options:
a.
Use the Advertiser table for the subform.
b.
Select the AdvertiserID, AdvertiserName, and Current Due fields from the
Advertiser table to add to the subform.
c.
Accept the default link (“Show Advertiser for each record in AdRep using
AdRepNumber”) to link the main form to the subform.
d.
Save the subform as Advertiser subform (which is the default name).
e.
If necessary, reposition the subform to match the approximate location
shown in Figure 3a on the following page and Figure 3b on page 5.
Save and close the form.
Shelly Cashman Access 2016 | Module 5: SAM Project 1a
Figure 3a: AdRep Master Form – Form View
Shelly Cashman Access 2016 | Module 5: SAM Project 1a
Figure 3b: AdRep Master Form – Design View
8.
Open the State-Billboard Master Form in Design View, and then modify it to
match Figure 4a on the following page and Figure 4b on page 7 by resizing the
width of the subform as shown in the figures. (Hint: The right edge of the
subform is at the 5.5" ruler mark.) Save, but do not close the form.
9.
With the State-Billboard Master Form still open in Design View, modify the form
title to match Figures 4a and 4b by completing the following tasks:
a.
Change the font color of the title to Dark Red (1st column, 7th row of the
Standard Colors palette).
b.
Change the font size of the title text to 20 pt.
c.
Apply the Raised special effect to the title.
Save and close the form.
Shelly Cashman Access 2016 | Module 5: SAM Project 1a
Figure 4a: State-Billboard Master Form – Form View
Shelly Cashman Access 2016 | Module 5: SAM Project 1a
Figure 4b: State-Billboard Master Form – Design View
10.
Open the Rentals Financial Form in Design View, and then modify the form by
completing the following tasks:
a.
Delete the Discount control and the accompanying label.
b.
Change the label for the Facing control to Direction. (Hint: Do not type
the period.)
Save and close the form.
11.
Open the Rentals Contact Form in Design View, and then modify the form to
match Figure 5 on the following page by completing these tasks:
a.
Select the BillboardID, Cost, Months, and BeginDate controls and labels
simultaneously.
b.
Adjust the spacing of these fields to have equal vertical spacing.
Save, but do not close the form.
Shelly Cashman Access 2016 | Module 5: SAM Project 1a
12.
With the Rentals Contact Form still open in Design View, change the tab order
so that users tab to the AdvertiserName field immediately after the RentalID
field. (Hint: Move the AdvertiserName field upward in the tab order.) Save and
close the form.
Figure 5: Rentals Contact Form – Design View
13.
Open the Basic Billboard Form in Design View, and then change the background
color of the form to Light Gray 2 (1st column, 3rd row of the Standard Colors
palette). Save the form, but do not close it.
14.
With the Basic Billboard Form still open in Design View, modify the form to
match Figure 6 on the following page by performing these tasks:
a.
Select the Location label, Location control, Height label, and Height
control simultaneously. Align the controls to the top.
b.
Select the Location control, City control, State control, and Type control
simultaneously. Do not select any labels, and make sure the Height
control is not selected. Align the controls to the left.
Shelly Cashman Access 2016 | Module 5: SAM Project 1a
Save, but do not close, the form.
15.
With the Basic Billboard Form still open in Design View, add a title to the form
using Basic Billboard Form as the name. (Hint: This is the default name).
Save, but do not close the form.
16.
With the Basic Billboard Form still open in Design View, change the Tab Stop
property to No for the SqrFt field. Save and close the form.
Figure 6: Basic Billboard Form – Design View
17.
Open the AdRep Skills Query in Design View, and then modify the query to find
all ad reps who were hired after 1/1/2016 and who have special skills in
Spanish. (Hint: The Special Skills field has the Long Text data type. The query
should return one record.) Run the query, and then save and close it.
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.
Purchase answer to see full
attachment