SC_AC16_9a

Access Database Assignment 

Shelly Cashman Access 2016 | Module 9: SAM Project 1a

Shelly Cashman Access 2016 | Module 9: SAM Project 1a

West Coast Outdoor Advertisers

ADMINISTeRING A DATABASE SYSTEM

GETTING STARTED

· Open the file SC_AC16_9a_FirstLastName_1.accdb, available for download from the SAM website.

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

2. Hint: 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.

· To complete this Project, you will also need to download and save the following support file from the SAM website:

· Support_SC_AC16_9a_BillboardTypes.accdb

· 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

Open the database in Backstage View, and then click the View and edit database properties link. When the Properties dialog box displays, create a custom property for the database as described below:

a. Create a custom property named Rent. (Hint: Do not type the period.)

b. Select Date as the type.

c. Enter 08/08/2016 as the value.

Confirm that your custom property matches the one shown in Figure 1 on the next page. (Hint: Your database may contain other properties, depending on the version of Access used to open the file or the method by which the file was downloaded from SAM. Ignore these additional properties.) Save and close the Properties box.

Figure 1: Custom Properties Tab in the Properties Dialog Box

With the database still open in Backstage View, click the Options tab. Select the Main Menu Navigation Form as the display form for the database, as shown in Figure 2 on the next page. (Hint: The display form is sometimes referred to as the startup form.)

Save the changes to the options. (Hint: When the Access dialog box appears and displays the message, “You must close and reopen the current database for the specified option to take effect.”, click the OK button.)

Figure 2: Access Options Dialog Box

Open the AdRep table in Design View, and then create a custom input mask for the AdRepNumber field as described below:

d. The AdRepNumber field must consist of two letters and two numbers.

e. All letters should display as uppercase. (Hint: Enter >LL99 as the input mask.)

f. Save the changes to the table.

With the AdRep table still open in Design View, modify the AdRepName multiple-field index by completing the following tasks:

g. Open the Indexes dialog box.

h. Change the sort order for the LastName field to Descending.

i. Save the changes to the table, and then close it.

Open the Advertiser table in Design View, and then create a single-field index on the AdvertiserName field using AdvertiserName as the index name. (Hint: Be careful not to include spaces after the index name.) The index should allow duplicate values. Save the changes to the table design.

With the Advertiser table still open in Design View, create a multiple-field index as described below:

j. Name the index AdvertiserLocation. (Hint: Be careful not to include spaces after the index name.)

k. Use the State field, sorted in Descending order, as the first field in the index.

l. Use the City field, sorted in Ascending order, as the second field in the index.

m. Save the changes to the table design, and then close the table.

Open the Rentals table in Design View, and then open the Property Sheet for the table and make the following updates:

n. Create a validation rule for the table requiring that the Deposit field value is always less than or equal to the Cost field value. (Hint: Enter [Deposit]<=[Cost] as the validation rule.)

o. Enter Deposit cannot exceed Cost as the validation text.

Close the Property Sheet, and then save the changes to the table. (Hint: Because there was a change to data integrity rules, the “existing data may not be valid” warning message will appear. Click Yes and continue saving the table. If another message occurs, click the Yes button.)

With the Rentals table still open in Design View, create a multiple-field index as described below:

p. Name the index RentalsInformation. (Hint: Be careful not to include spaces after the index name.)

q. Use the AdvertiserID field, sorted in Ascending order, as the first field in the index.

r. Use the RentalID field, sorted in Ascending order, as the second field in the index.

s. Save the changes to the table, and then close it.

Create a Datasheet form for the Advertiser table, and then save the form with the name AdvertisersForm. (Hint: Do not type the period.) Close the form.

Create a blank form based on the 1 Right application part. (Hint: The blank form will appear in the Navigation Pane as a forms object named “SingleOneColumnRightLabels” and is saved automatically.)

Import a table from an encrypted database by completing the following steps:

t. Open the encrypted database Support_SC_AC16_9a_BillboardTypes.accdb, available for download from the SAM website, in Exclusive mode, using the password billboard when the Enter Password dialog box appears. Enable the content.

u. Decrypt the Support_SC_AC16_9a_BillboardTypes.accdb database. (Hint: You will need to use the password billboard again.) Close the database.

v. Open (or return to) the SC_AC16_9a_FirstLastName_2.accdb database.

w. Import the BillboardType table from the database Support_SC_AC16_9a_BillboardTypes.accdb into a new table with the name BillboardType. (Hint: Ensure that the Tables tab is selected in the Import Object dialog box.)

x. Do not save the Import steps.

Use the Table Analyzer to analyze the Rentals table for redundancy. Implement the following options when using the Table Analyzer Wizard:

y. Select the Rentals table as the table to analyze, and let the wizard decide what fields go in each table.

z. Rename Table 1 as AltRentals using the Rename table button, as shown in Figure 3 on the next page.

aa. Rename Table 2 as AltMonths using the Rename table button, as shown in Figure 3 on the next page.

ab. Rename Table 3 as AltFacing using the Rename table button, as shown in Figure 3 on the next page.

ac. When the wizard displays the next screen with the question, “Do the bold fields uniquely identify each record in the proposed table?”, click the Next button.

ad. When the wizard displays the next screen, “Correcting typographical errors for table ‘AltMonths’.”, click the Next button. Ensure that the “No, don’t create a query” radio button is selected, then click Finish to close the Table Analyzer Wizard.

ae. When the Table Analyzer Wizard dialog box appears with the message, “The command or action TileHorizontally isn’t available now”, click OK. Close the AltRentals, AltMonths, and AltFacing tables.

af. Confirm that the Navigation Pane matches Figure 4 below, and then save and close the form.

ag.

Figure 3: Table Analyzer Wizard

Figure 4: Navigation Pane with Tables Created in Step 13

Switch to viewing database items by the custom category named “Rental Information” in the Navigation Pane. Add the following two forms to the Rental Information Form group:

ah. Rentals

ai. Rentals and Billboards Form

Confirm the Rental Information Form group matches Figure 5 below.

Add a new group to the Rental Information category as described below:

aj. Name the new group Rental Reports.

ak. If necessary, move the Rental Reports group so that it appears between the Rental Information Form and the Unassigned Objects group.

al. In the Navigation Pane, add the Basic Billboards List report to the new group.

Confirm that the Rental Reports group and item in the Navigation Pane match Figure 5.

Figure 5: Navigation Pane Viewed by Rental Information Category

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.

2

_Status

																																																									IfSuccessful	GA_Status_Icon	SAM_Logo
				true
																					false

Score

														ID		FirstName		LastName	ProjectName	SubmissionNum		Max	Score	EngineVersion

ID

IfSuccessful

StepNumber	Description	StepScore	StepMaxScore	ErrorText	ActionName	StepActionOrder

LastName FirstName

Number

0

ID

0

OR

0

Max

Portland OR

WA

Fluent in Spanish

	AdRepNumber		Address			City				State		PostalCode		Phone	Salary	DateHired	Insurance	Special Skills
			AM01	Miller	Abraham	54 Quarry Ave.					Portland																																		OR	97210	503-555-6032	¤ 32,5	40.0	6/1/16		Fluent in Spanish
				GZ01	Zdalnza	Gregory	7 Moose Head Rd.			Boise		83720	603-555-8765	¤ 29,67		5.0	9/14/16	Trains new employees
			KS02	Stenhope	Kathryn	9 Shennadoah Ave.					Salem	97311	503-555-0003	¤ 34,57					6.0	10/5/15	Bachelor’s degree in Marketing
				MR01	Riley	24 Springtown Rd.				Casper									WY				83114	781-555-2323	¤ 30,450.00	8/6/16	Likes to travel; makes routine checks of billboards
			RH01	Horton	Rowan	12 Heather Rd.	97211	207-555-9009	¤ 35,275.00	7/13/15	Bachelor’s degree in Business Administration
			SL01	Lorens	Stephanie	15 River Rd.		Spokane															WA		99202	802-555-3339	¤ 37,350.00	4/5/15	Working on a Master’s degree in Communications
			UG01	Grea-Mayers	Ursula	342 Pine St.			Seattle		98101	207-555-8534	¤ 41,765.00	12/12/14

Address City State PostalCode Phone

AdRepNumber

tes

Seattle WA

UG01

Community College

Burns OR

RH01

Boise ID 83720

GZ01

WA

SL01

Portland OR

RH01

ern Wear

Casper WY 83114 ¤ 0.00

MR01

OR

0

GZ01

Grants Pass OR 97526

¤ 2,460.75 AM01

OR

AM01

OR

RH01

Spokane WA

SL01

Outdoor Adventures

ID

KS02

WA 99202

UG01

WY 83114

0

MR01

42

WA

¤ 2,500.00

SL01

ID

MR01

Salem OR

KS02

Seattle WA 98101

UG01

WA

¤ 1,200.00

GZ01

Portland OR 97209

¤ 0.00 AM01

Casper WY 83114

¤ 2,500.00 MR01

Kettle Falls WA 99141

¤ 0.00 GZ01

OR

KS02

	AdvertiserID	AdvertiserName	Amount Paid	Current Due	Customer												No
BBF32	Babbage CPA Firm	464 Linnell Dr.	98104	¤ 5,678.25	¤ 7,875.25
	BCC76			Burns	867 Ridge Ave.	97720																													¤ 0.00	¤ 5,452.30
		BMS53	Boise Medical Services	134 Main St.	6035552883	¤ 7,562.40	¤ 6,152.25	Designs own ads
	CDS29	Carter Dental Services	123 Second St.		Dallesport	98617	¤ 3,452.10	¤ 5,475.75
	CRR15	Columbia River Rafters	125 River Blvd.		97209	¤ 4,567.25	¤ 3,600.50
	CWW01	Casper				West	10 Broadway St.	¤ 4,125.50
	GAD74	Grant Antique Dealers	78 Catawba Dr.			Grants Pass		97526	¤ 1,500.00		¤ 1,		200.0
	GPM12	Grants Pass Motel	235 Second Ave.		¤ 2,460.75
	GRI03	Germann Inn	345 Hartsel Ave.		Madras	97741	¤ 1,325.45	¤ 2,900.50
	HAR01	Haydee’s Restaurant	234 Twiddy St.				Eugene	97402		¤ 3,000.00				¤ 2,500.00
	HCH10	Hendley County Hospital	216 Rivard Blvd.	99201	¤ 9,456.75	¤ 7,234.60
		IOA23		Idaho	12 Third St.		Twin Falls	83301	¤ 4,200.00	¤ 6,500.00
	KDS21	KAL Design Studio	116 Pine St.		Arden			¤ 775.00	¤ 1,550.50
		KGS04	Kyle General Store	421 First St.		Cokeville	¤ 3,45				2.0	¤ 2,875.50
		L	CA	Lews and Clark Adventures	100 Main St.		Goldendale	98620	¤ 4,500.00
		LFS02	Lewis Furniture Store	267 Main St.		Payette	83661	¤ 1,234.50	¤ 2,890.70
		MBT14	Mike’s Bike Tours	234 Gilham St.	97301	¤ 1,090.75	¤ 1,345.60
PJG34	Patricia Jean Greenhouses	345 Magee Ave.	¤ 1,345.00	¤ 2,450.00
	RER23	Rivers Edge Restaurant	125 River Rd.			Kettle Falls		99141	¤ 1,375.00
	SLA77	Smarter Law Associates	764 Main St.	¤ 7,500.00
TVS17	Theon Veterinary Services	346 Austin Ave.	¤ 2,225.60
WEC05	Western Energy Company	12 Polk St.	¤ 3,450.80
	WSC01	Wood Sports Complex	578 Central Ave.		Cave Junction	97523	¤ 2,345.50	¤ 3,590.80

City State

Arden WA

OR 8.0 24.0 192.0 Yes

Digital

Hwy 395 Burns OR

Yes

Cave Junction OR 6.0

No

-26

Casper WY

No

Hwy 97

OR 10.0 24.0

Yes

Digital

ID 8.0 24.0 192.0 No

Poster

Cokeville WY 10.0 20.0 200.0 No 506 Poster

Dallesport WA 8.0 20.0

No

Junior Poster

Hwy 89

WY 6.0 8.0 48.0 No

Junior Poster

WY 8.0 24.0 192.0 Yes

Poster

OR 10.0

Yes

Bulletin

Grants Pass OR 10.0

Yes

Poster

Hwy 395 Kettle Falls WA 6.0 10.0 60.0 No

Junior Poster

Hwy 97

OR 10.0 24.0 240.0 Yes

Digital

Hwy 97 Madras OR 8.0 24.0 192.0 Yes

Digital

Hwy 97

OR 8.0 24.0 192.0 Yes

Poster

Payette ID

24.0

No

Bulletin

Hwy 95

ID 8.0 24.0 192.0 Yes

Digital

Hwy 20

ID

48.0

Yes

Bulletin

OR 5.0 8.0 40.0 No

Junior Poster

Salem OR 8.0 24.0 192.0 Yes

Poster

Salem OR

Yes

Poster

Hwy 99 Salem OR 8.0 24.0 192.0 Yes 10224 Poster

Hwy 93 Twin Falls ID 6.0 12.0 72.0 No

Junior Poster

Hwy 95

ID 10.5 24.0

No

Poster

ID 14.0 48.0

Yes

Bulletin

Boise ID

Yes

Bulletin

OR 12.0 48.0

Yes

Bulletin

Portland OR 14.0 48.0 672.0 Yes

Bulletin

OR 8.0 25.0 200.0 Yes

Digital

Eugene OR 10.0 24.0 240.0 Yes

Poster

Eugene OR 12.0 24.0 288.0 Yes

Poster

Eugene OR 10.0 24.0 240.0 Yes

Digital

Goldendale WA 8.0 16.0

No 1748 Digital

Klamath Falls OR 8.0 24.0 192.0 Yes

Poster

OR 7.0 14.0

Yes

Junior Poster

	BillboardID	Location	Height	Width	SqrFt	Illuminated	DEC	Type
		LHAR04			Hwy 395																8.0																	2					4.0										192.0																									Yes	4395									Digital
LHBE01					Hwy 97	Beaver Marsh	3542
	LHBU01		10.5						48.0	504.0	1509													Poster
	LHCA01	Hwy 199		16.0	96.0	2024							Junior Poster
	LHCA03		Hwy 20											10.0	32.0	3		20.0	1288								Bulletin
LHCH03	Chemult				240.0	3411
	LHCH05		Hwy 93	Challis		506
	LHCO05		Hwy 89
	LHDA03	Hwy 197	1	60.0	3700
LHDC07	Alpine	1964
	LHDD01	Hwy 26	Dubois	1016
LHGR01	Hwy 18	Grand Ronde	30.0	300.0	5182
		LHGR02		Hwy 99		25.0	250.0	5838
		LHKE01	7400
LHKL01		Klamath Falls	4461
	LHMA01	12070
LHMP01	Modoc Point	5500
		LHPA03			Hwy 95				12.0		288.0	3777
LHRI01	Riggins	1724
LHRI02	Rigby				14.0	6	72.0	13406
LHRO01	Hwy 101	Rockaway	1702
LHSA01	Hwy 114		10224
		LHSA03	Liberty Road	9.0	2		7.0	24						3.0	11808
LHSA05
	LHTW01	1564
	LHWE01	Weiser	252.0		1748
LHWE04	I-90 Exit 60	Wallace		672.0	6888
	LIBO01	I-84 Exit 57	2				1.0	47.0	987.0	14104
LIRO01	I-5 Exit 120	Roseburg	576.0	27618
		LNPO01	I-84 Exit 1	110667
LSCO05	Hwy 42	Coquille	5642
	LSEU01	6th Ave.	19942
LSEU05	Maxwell St.	5707
LSEU07	W. 11th St.	21910
	LSGO01	Hwy 142	128.0
LSKL01	S. 6th St.	11414
LSSP01	Hwy 126	Springfield	98.0	10037

BillboardID

AdvertiserID

LHBU01

¤ 775.00 ¤ 0.00 3.0

false BCC76

LNPO01

¤ 0.00 4.0

false CRR15

LNPO01 West

¤ 0.00 8.0

true SLA77

LHAR04 North ¤ 2,500.00 ¤ 0.00 1.0

false KDS21

LHSA03 North

¤ 0.00 2.0

false MBT14

LHSA03

¤ 800.00 ¤ 0.00 2.0 7/1/17 false MBT14

LHGR02 North

¤ 0.00 2.0

false GAD74

LHTW01 North

¤ 0.00 5.0

false IOA23

LHDA03 South

¤ 0.00 6.0 6/5/17 true CDS29

LHCO05 North

¤ 0.00 4.0

false KGS04

LHWE01 South

¤ 0.00 1.0 9/12/17 false BMS53

LSGO01 West

¤ 0.00 10.0

false LCA42

LHMA01 South ¤ 3,000.00 ¤ 0.00 3.0 6/5/17 false GRI03

LHCA03 West

¤ 0.00 4.0

true CWW01

LHCA01 North

¤ 0.00 7.0

false WSC01

LHPA03 South

¤ 0.00 3.0

false LFS02

LSEU01 East ¤ 775.00 ¤ 0.00 2.0 7/1/17 false HAR01

LHDD01 East

¤ 0.00 1.0

false KGS04

LHKE01 South

¤ 0.00 3.0

false HCH10

LHAR04 South

¤ 0.00 10.0 2/1/17 false LCA42

LHKE01 North

¤ 0.00 3.0

false RER23

LHCH05 North

¤ 0.00 4.0

true IOA23

LIBO01 West

¤ 0.00 4.0 7/1/17 false BMS53

LHPA03 North ¤ 1,625.00 ¤ 0.00 3.0

false LFS02

LHGR02 North ¤ 790.00 ¤ 0.00 1.0 2/1/17 false GPM12

RentalID	Facing	Cost	Deposit	Months	BeginDate	Discount	Rental Contract
1700200											North			6/5/17
1700201			East	¤ 1,600.00	5/2/17
1700202	¤ 1,400.00	3/14/17
1700204	9/4/17
1700205		¤ 800.00				7/1/17
1700206							South
1700208		¤ 790.00		9/12/17
1700209	¤ 350.00	4/11/17
1700210	¤ 300.00
1700211	¤ 875.00	5/12/17
1700212	¤ 900.00
1700213	¤ 2,875.00			2/1/17
1700215
1700216	¤ 2,750.00	6/24/17
1700217	¤ 325.00	3/21/17
1700219		¤ 1,625.00	8/13/17
1700220
1700222	¤ 825.00	9/14/17
1700223	¤ 365.00	8/25/17
1700224	¤ 3,500.00
1700225	¤ 375.00	6/12/17
1700226	¤ 700.00	5/25/17
1700227	¤ 1,525.00
1700229	8/14/17
1700230

State

CA

ID Idaho
OR

WA

WY

StateName
California
NV	Nevada
Oregon
Washington
Wyoming

SELECT AdRep.AdRepNumber, AdRep.LastName
FROM AdRep
ORDER BY AdRep.LastName;
SELECT [AdRep].[AdRepNumber], [AdRep].[FirstName], [AdRep].[LastName]
FROM AdRep
ORDER BY [AdRepNumber];
SELECT DISTINCTROW *
FROM AdRep;
SELECT [Billboard].[BillboardID], [Billboard].[City]
FROM Billboard;
SELECT DISTINCTROW *
FROM AdRep;
PARAMETERS __BillboardID Value;
SELECT DISTINCTROW *
FROM [SELECT [Billboard].[BillboardID], [Billboard].[Location], [Billboard].[State] FROM Billboard]. AS [Rentals and Billboards Form]
WHERE ([__BillboardID] = BillboardID);
SELECT DISTINCTROW *
FROM AdRep;
SELECT DISTINCTROW *
FROM Advertiser;
SELECT DISTINCTROW *
FROM Billboard;
SELECT DISTINCTROW *
FROM States;
SELECT DISTINCTROW *
FROM Rentals;
SELECT DISTINCTROW *
FROM Rentals;
SELECT DISTINCTROW *
FROM States;
SELECT DISTINCTROW *
FROM States;
SELECT DISTINCTROW *
FROM Advertiser;
SELECT DISTINCTROW *
FROM Billboard;
SELECT AdRep.AdRepNumber, AdRep.FirstName, AdRep.LastName, AdRep.Address, AdRep.City, AdRep.State, AdRep.PostalCode, AdRep.PhoneNumber, AdRep.Salary, AdRep.DateHired
FROM AdRep;
SELECT AdRep.AdRepNumber, AdRep.LastName, AdRep.FirstName, AdRep.DateHired, AdRep.[Special Skills]
FROM AdRep
WHERE (((AdRep.DateHired)>#1/1/2016#) AND ((AdRep.[Special Skills]) Like “*Spanish*”));
SELECT AdRep.AdRepNumber, AdRep.FirstName, AdRep.LastName, Advertiser.AdvertiserID, Advertiser.AdvertiserName, Advertiser.[Amount Paid], Advertiser.[Current Due]
FROM AdRep INNER JOIN Advertiser ON AdRep.AdRepNumber = Advertiser.AdRepNumber;
SELECT AdRep.AdRepNumber, Advertiser.AdvertiserID, Rentals.RentalID, [Cost]*[Months] AS [Total Cost]
FROM (AdRep INNER JOIN Advertiser ON AdRep.AdRepNumber = Advertiser.AdRepNumber) INNER JOIN Rentals ON Advertiser.AdvertiserID = Rentals.AdvertiserID;
SELECT [FirstName] & ” ” & [LastName] AS StudentName, [_GradingReport].ProjectName, “Submission #” & [SubmissionNum] AS SubmissionCt, [_GradingReport].Score, [_GradingReport].MaxScore, “Score is: ” & [Score] & ” out of ” & [MaxScore] AS Grade, [_GradingReportSteps].[StepNumber] & “. ” & [Description] AS Step, [_GradingReportSteps].[StepScore] & “/” & [_GradingReportSteps].[StepMaxScore] AS StepGrade, [_GradingReportSteps].ActionName, IIf([ifsuccessful]=True,Null,[ErrorText]) AS Feedback, [_GradingReportSteps].IfSuccessful, [_GradingIcons].GA_Status_Icon, [_GradingReportSteps].StepNumber, [_GradingReportSteps].StepActionOrder, [_GradingReportSteps].ID, [_GradingIcons].SAM_Logo, [_GradingReportSteps].Description, [_GradingReport].EngineVersion
FROM _GradingReport, _GradingIcons INNER JOIN _GradingReportSteps ON [_GradingIcons].[IfSuccessful_Status] = [_GradingReportSteps].IfSuccessful;
SELECT Rentals.RentalID, Rentals.BillboardID, Rentals.Cost, Rentals.Months, [Cost]*[Months] AS TotalCost, Rentals.BeginDate, Advertiser.AdvertiserName, Rentals.AdvertiserID
FROM Advertiser INNER JOIN Rentals ON Advertiser.AdvertiserID = Rentals.AdvertiserID;
SELECT Rentals.RentalID, Rentals.BillboardID, Rentals.Cost, Rentals.Months, Rentals.BeginDate, Rentals.AdvertiserID, Advertiser.AdvertiserName, Advertiser.Phone
FROM Advertiser INNER JOIN Rentals ON Advertiser.AdvertiserID = Rentals.AdvertiserID;
SELECT Billboard.BillboardID, Billboard.Location, Billboard.City, States.StateName, Billboard.Illuminated, Billboard.DEC, Billboard.Type
FROM States INNER JOIN Billboard ON States.State = Billboard.State;