SC_AC16_8b

This is an Access Database assignment.

Shelly Cashman Access 2016 | Module 8: SAM Project 1b

Shelly Cashman Access 2016 | Module 8: SAM Project 1b

All Around Outside Maintenance

MACROS, NAVIGATION FORMS, CONTROL LAYOUTS

GETTING STARTED

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

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

0. 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.

1. 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 Preview Client Financial Report macro in Design View. Change the View argument to Print Preview. Save the change to the macro, and then close the Preview Client Financial Report macro.

The Open Basic Property List macro contains an error. Complete the following tasks to correct the macro:

a. Open the Open Basic Property List macro in Design View.

b. Click the Single-Step button.

c. Run the macro.

d. Correct the error. (Hint: The macro should open the Basic Property List report in Print Preview mode.)

e. Click the Single-Step button again to deselect it.

Save and close the Open Basic Property List macro.

Create a new macro with two submacros by following the directions below:

f. When the Macro Design window opens, display the Action Catalog.

g. Add the first submacro to the macro, using Open Property Table as the name for the submacro.

h. In the first submacro, use the OpenTable action to open the Property table in Datasheet View and in Edit data mode.

i. Add a second submacro to the macro, using Open Client Table as the name for the macro.

j. In the second submacro, use the OpenTable action to open the Client table in Datasheet View and in Edit data mode.

k. Save the macro with the name OpenTables.

Confirm that your macro matches Figure 1 below, and then close the macro.

Figure 1: OpenTables Macro

Open the Client Types table in Datasheet View, and then create a data macro for the table by completing the following tasks:

l. Click the Before Change button on the Table Tools tab.

m. Create the macro shown in Figure 2 on the next page.

Save and close the macro, and then save and close the Client Types table.

Figure 2: Data Macro for a Before Change Event

Create a Datasheet form based on the State table, and then save the form with the default name State. Close the form.

Create a Navigation form for the database with the following options:

n. Use the Horizontal Tabs layout.

o. Add the Manager, Client, and Property Datasheet forms to the Navigation form in that order.

p. Change the title (in the Form Header) to Basic Navigation Form. (Hint: Do not type the period.)

q. Save the navigation form with the name Basic Navigation Form. (Hint: Do not type the period.)

Confirm that your form matches Figure 3 on the next page. Save and close the Basic Navigation Form.

Figure 3: Basic Navigation Form – Form View

Open the Client form in Datasheet View, and then complete the following tasks to create a UI macro for the form:

r. Select the ClientNumber column. (Hint: The ClientNumber column has the caption “CL#”.)

s. Click the Property Sheet button.

t. Click the Build button for the On Click event, and then select Macro Builder.

u. Create a macro that opens the Client Master Form when a user selects a value in the ClientNumber column. The macro actions are shown in Figure 4 on the next page.

Save and close the macro, and then save and close the form.

Figure 4: UI Macro Associated with an On Click Event in the Client Form

Open the Main Menu Navigation Form in Layout View, and then complete the following tasks:

v. Add the Forms List form to the Main Menu Navigation Form as the last horizontal tab.

w. Rename the Forms List tab as Master Forms. (Hint: Do not type the period.)

x. Move the Manager tab so that it appears first in the list.

Confirm that the form matches Figure 5 on the next page, and then save and close the form.

Figure 5: Main Menu Navigation Form – Form View

Open the Open Master Forms form in Design View, and then add a command button to the form in the approximate position shown in Figure 6 on the next page. Use the Command Button wizard to create the command button while incorporating the following options:

y. Select Miscellaneous as the category and Run Macro as the action.

z. Select Forms Macro.Open Property Master Form as the macro.

aa. Select the Text option, and then enter the text Open Property Master Form. (Hint: Do not type the period.)

ab. Name the command button Open_Property_Master_Form. (Hint: Do not type the period.)

Save the changes to the form.

With the Open Master Forms form still open in Design View, complete the following tasks:

ac. Select the three buttons on the form, and then use the Size/Space menu to adjust the size to To Widest.

ad. Use the Size/Space menu to adjust the spacing of the button controls to Equal Vertical.

ae. Align the buttons to the Left.

Confirm that your form matches Figure 6, and then save and close the Open Master Forms form.

Figure 6: Open Master Forms Form – Design View

Open the Manager Layout Form in Layout View, and then modify the form to match Figure 7 on the next page by completing the following tasks:

af. Select the cell containing the value for the FirstName field, and then split the cell horizontally.

ag. Delete the LastName label.

ah. Move the cell containing the value for the LastName field to the position shown in Figure 7 (to the right of the FirstName field in the cell created in Step 11a).

ai. Change the FirstName label to Manager Name (including the space), as shown in Figure 7.

aj. Move the StartDate label and StartDate field to the position shown in Figure 7.

ak. Select the control layout for the form, and then change the control margins to Medium and the control padding to Narrow.

Save and close the form.

Figure 7: Manager Layout Form – Layout View

Open the States and Properties Tabular Report in Layout View. Select the control layout, and then change the report layout to Stacked as shown in Figure 8 on the next page. (Hint: To select a control layout, click the four-headed arrow in the upper-left corner of the report.) Save the change to the report, and then close it.

Figure 8: States and Properties Tabular Report – Layout View

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

false

{FC051A26-2EBD-43D5-A9D0-0DF063214E8B}

		ID			FirstName			LastName	AssignmentGUID	UserID
Alicia	Kraemer		{FC051A26-2EBD-43D5-A9D0-0DF063214E8B}

ID FirstName LastName

Score

ProjectName	SubmissionNum		Max	Score	EngineVersion

ID

IfSuccessful

StepNumber		Description	StepScore	StepMaxScore	ErrorText	ActionName	StepActionOrder

tes

Chadds Ford

Group

Cherry Hill

Storage Facilities

Claymont

Service Stations

DE

Commercial KS02

Community College

Elkton

Corporate

Coatesville PA

Commercial

Elkton MD

Residential

Retirement Community

Gwynedd PA

Residential

PA

¤ 10,952.00 Commercial

PA

Residential

NJ

¤ 0.00

¤ 1,280.75 Commercial SL01

PA

Commercial RH01

NJ

Corporate

Condominium Association

Norristown PA

Residential

Philadelphia PA

Residential RH01

DE

¤ 0.00 ¤ 34,546.00 Commercial KS01

College System

Springfield PA

¤ 0.00 ¤ 2,500.25 Corporate MR01

PA

Commercial GZ01

Malvern PA 19355 ¤ 0.00 ¤ 0.00 ¤ 0.00 Residential GZ01

Wilmington DE

Corporate KS02

Mall

West Chester PA

Commercial RH01

	ClientNumber	ClientName			Address			City				State		PostalCode	Phone	AmountPaid	Balance	TotalAmount	ClientType		ManagerID	Client											No
	CFH01			Chadds Ford	HOA		29 Prospect St.																																		PA	19317	¤ 24,752.00	¤ 3,875.25	¤ 28,627.25								Residential				KS02
	CHG05				Cherry Hill	9 Harrington Ave.											NJ		08002	8565552883	¤ 29,837.00	¤ 2,765.30	¤ 32,602.30						Corporate			SL01	Manages condo complexes
	CSS01				Claymont	18 Barton St.														DE		19703	¤ 30,841.00	¤ 2,575.50	¤ 33,416.50										Commercial		DH01
				DSS02		Delaware	103 Hubbard Way		Newark	19702	¤ 27,152.00	¤ 2,875.00	¤ 30,027.00
	ECC06						Elkton		134 Lincoln Rd.								MD		21921	¤ 19,620.00	¤ 1,875.00	¤ 21,495.00			KS01
		FBC04	First Bank of			Coatesville		290 Brook Mill Rd.		19320	¤ 25,702.00	¤ 3,016.75	¤ 28,718.75		AM01
		FRR02	Fitzmeyer Rental Properties	35 Washington St.		21922	¤ 19,740.00	¤ 2,095.00	¤ 21,835.00		UG01
GRC01		Gwynedd	302 Great Rd.	19436	¤ 34,036.00	¤ 580.00	¤ 34,616.00			MR01
		HRG01	Halko Restaurant Group	1294 Monument Ave.				Media		19063		¤ 10,952.00							¤ 0.00					RH01
	KRC02	Kennett Retirement Community	7 Lakeview Rd.		Kennett Square	19348	¤ 44,026.00	¤ 3,590.80	¤ 47,616.80				GZ01
		MCC02	Mike’s Convenience Stores	17 Briarwood Ln.				Camden	08101		¤ 1,280.75
				MHS01	Mary’s Hoagies and Steaks	346 Magee Ave.						Philadelphia	19111	¤ 21,769.00	¤ 2,890.60	¤ 24,659.60
		MIG01	MidAtlantic Insurance Group		1702 Hunter Ave.			Pennsauken		08109	¤ 13,097.00	¤ 2,450.00	¤ 15,547.00		BR01
	NCA03			Norristown	29 Newtown Circle		19401	¤ 22,697.00	¤ 3,480.45	¤ 26,177.45		ER01
	OTR03	Old Town Rentals	682 Hickory Way	19147	¤ 15,345.00	¤ 2,875.50	¤ 18,220.50
		RDS03	Roehr Department Stores	913 Main St.							Wilmington		19810		¤ 34,546.00
SCC03				Springfield	41 Carver Hill Rd.		19064		¤ 2,500.25
		SRG01	Starvos Restaurant Group	3 Pleasant St.			Malvern		19355	¤ 42,561.00	¤ 12,040.00	¤ 54,601.00
VMA01	Villa Maria Apartments	200 Queen Rd.
	WBT01	Wilmington Bank & Trust		52 Putnam Rd.	19806	¤ 35,640.00	¤ 9,875.40	¤ 45,515.40
	WCM01					West Chester	456 Selwyn Ave.		19380	¤ 46,540.00	¤ 13,450.00	¤ 59,990.00

Description

Commercial

Corporate

HOA

¤ 500.00

Residential

TypeID	MinRate	Deposit
COM	¤ 1,000.00
CRP	¤ 2,500.00
GVT	Government		¤ 500.00
Home Owners Assn
RES	¤ 800.00

ManagerID FirstName LastName Address City State PostalCode

AM01

Coatesville PA 19320

No

BR01

Pennsauken NJ 08109

No

, AA degree

DH01

Claymont DE 19703

No

ER01

Norristown PA 19401

1/11/16

No

GZ01

West Chester PA 19380

No

KS01

Elkton MD 21921

No

KS02

Wilmington DE 19810

No

MR01 Max

Springfield PA 19064

No landscaping

RH01 Rowan

Media PA 19063

No construction management

SL01

Cherry Hill NJ 08002

No

UG01

MD 21922

No general contracting

PhoneNumber	StartDate	Salary	CPRCert	Skills
Alex	Middleton	54 Quarry Ave.	610-555-6032	8/1/16	¤ 32,450.90	Spanish, carpentry
Belinda		Rowan	140 Oak Stump Rd.	856-555-2134		1/11/16	¤ 36,575.75		landscaping
Devyn	Harlier	51 Abby Rd.	302-555-7767	8/3/15	¤ 40,320.00	licensed electrician
Eric	Rogers	103 Main St.	610-555-8295	¤ 38,767.20		general contracting
Gregory	Zolda	7 Moose Head Rd.	610-555-8765	12/7/15	¤ 42,540.00		construction management
Kevin	Stasner	143 Oak Stump Rd.	410-555-1230	4/6/15	¤ 46,575.25	carpentry, painting
Kaitlyn	Stenhope	9 Shennadoah Ave.	302-555-0003	4/18/16	¤ 37,564.20	electrician, landscaping
Riley	24 Spring Rd	610-555-2323	2/2/15	¤ 39,875.00
Horton	12 Heather Rd.	610-555-9009	9/21/15	¤ 41,320.00
Stephanie	Lorenz	15 River Rd.	856-555-3339	6/6/16	¤ 33,456.75	road work
Ursula	Grayson	342 Pine St.	Fair Hill	410-555-8534	3/7/16	¤ 38,765.30

Address City State

ClientNumber

Camden NJ MIG01

Camden NJ MCC02

Camden NJ MCC02

29 Prospect St. Chadds Ford PA CFH01

Cherry Hill NJ CHG05

Claymont DE CSS01

290 Brook Mill Rd. Coatesville PA FBC04

MD FRR02

PA FBC04

134 Lincoln Rd. Elkton MD ECC06

Ave.

Elkton MD FRR02

Kennett Square PA KRC02

Malvern PA SRG01

Media PA HRG01

Media PA MHS01

DE RDS03

Newark DE DSS02

PA NCA03

1702 Hunter Ave. Pennsauken NJ MIG01

Philadelphia PA MHS01

Philadelphia PA MHS01

Philadelphia PA MHS01

Philadelphia PA OTR03

PA SRG01

Springfield PA HRG01

West Chester PA RDS03

West Chester PA WCM01

Wilmington DE DSS02

Wilmington DE DSS02

Wilmington DE DSS02

52 Putnam Rd. Wilmington DE WBT01

PropertyID	Services
PCA001	123 Oakley St.
PCA005	14 Congress St
PCA006	100 Morgan Ave.
PCF001
PCH001	Cherry Hill Mall
PCL001	234 Fourth St.
PCO001
PDA001	230 First St.	Darlington
PDW001	123 Major Rd.	Downington
PEC001
PEL001	100	Maryland
PKS001	Route 1
PMA001	102 Main St.
PME001	23 Chester Blvd.
PME005	789 Valley St.
PNC001	100 Main St.	New Castle
PNE001	100 Second St.
PNR001	29 Newton Circle	Norristown
PPE001
PPH001	346 Bingham St.
PPH002	200 Robbins Ave.
PPH003	1234 Rising Sun Ave.
PPH006	100 Elfreth’s Alley
PPM001	234 Washington St.	Plymouth Meeting
PSP001	Springfield Mall
PWC001	225 Olympia Rd.
PWC003	West Chester Mall
PWI001	102 Townsend St.
PWI002	Route 202
PWI003	Route 52
PWI005

State

DE Delaware
MD Maryland
NJ

PA

StateName
New Jersey
NY	New York
Pennsylvania
VA	Virginia

SELECT [Manager].[ManagerID], [Manager].[FirstName], [Manager].[LastName]
FROM Manager
ORDER BY [ManagerID];
PARAMETERS __ManagerID Value;
SELECT DISTINCTROW *
FROM Client AS [Manager Client Data Form]
WHERE ([__ManagerID] = ManagerID);
SELECT DISTINCTROW *
FROM Client;
SELECT DISTINCTROW *
FROM Manager;
SELECT DISTINCTROW *
FROM Manager;
SELECT DISTINCTROW *
FROM Property;
SELECT DISTINCTROW *
FROM State;
SELECT DISTINCTROW *
FROM Property;
SELECT DISTINCTROW *
FROM Client;
SELECT DISTINCTROW *
FROM Client;
SELECT Property.PropertyID, Property.Address, Property.City, Property.State, Client.ClientName, Client.Phone
FROM Client INNER JOIN Property ON Client.ClientNumber = Property.ClientNumber;
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 Manager.ManagerID, Manager.FirstName, Manager.LastName, Manager.StartDate, Manager.Skills
FROM Manager
WHERE (((Manager.StartDate)<#1/1/2016#) AND ((Manager.Skills) Like "*carpentry*")); SELECT Manager.ManagerID, Manager.FirstName, Manager.LastName, Client.ClientNumber, Client.ClientName, Client.AmountPaid, Client.Balance FROM Manager INNER JOIN Client ON Manager.ManagerID = Client.ManagerID; SELECT Manager.ManagerID, Manager.FirstName, Manager.LastName, Client.ClientNumber, Client.ClientName, Client.AmountPaid, Client.Balance, Property.PropertyID, Property.Address, Property.City, Property.State FROM (Manager INNER JOIN Client ON Manager.ManagerID = Client.ManagerID) INNER JOIN Property ON Client.ClientNumber = Property.ClientNumber; SELECT Property.PropertyID, Property.Address, Property.City, State.StateName, Property.Services, Property.ClientNumber, Property.State FROM State INNER JOIN Property ON State.State = Property.State;