Access Database assignment.

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

 

C:\Users\akellerbee\Documents\SAM Development\Design\Pictures\g11731.png 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 ManagerClient, 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.

IfSuccessful_Status GA_Status_Icon SAM_Logo
true
false
ID FirstName LastName AssignmentGUID UserID
false Alicia Kraemer {FC051A26-2EBD-43D5-A9D0-0DF063214E8B} {FC051A26-2EBD-43D5-A9D0-0DF063214E8B}
ID FirstName LastName ProjectName SubmissionNum MaxScore Score EngineVersion
ID StepNumber Description IfSuccessful StepScore StepMaxScore ErrorText ActionName StepActionOrder
ClientNumber ClientName Address City State PostalCode Phone AmountPaid Balance TotalAmount ClientType ManagerID Client Notes
CFH01 Chadds Ford HOA 29 Prospect St. Chadds Ford PA 19317 ¤ 24,752.00 ¤ 3,875.25 ¤ 28,627.25 Residential KS02
CHG05 Cherry Hill Group 9 Harrington Ave. Cherry Hill NJ 08002 8565552883 ¤ 29,837.00 ¤ 2,765.30 ¤ 32,602.30 Corporate SL01 Manages condo complexes
CSS01 Claymont Storage Facilities 18 Barton St. Claymont DE 19703 ¤ 30,841.00 ¤ 2,575.50 ¤ 33,416.50 Commercial DH01
DSS02 Delaware Service Stations 103 Hubbard Way Newark DE 19702 ¤ 27,152.00 ¤ 2,875.00 ¤ 30,027.00 Commercial KS02
ECC06 Elkton Community College 134 Lincoln Rd. Elkton MD 21921 ¤ 19,620.00 ¤ 1,875.00 ¤ 21,495.00 Corporate KS01
FBC04 First Bank of Coatesville 290 Brook Mill Rd. Coatesville PA 19320 ¤ 25,702.00 ¤ 3,016.75 ¤ 28,718.75 Commercial AM01
FRR02 Fitzmeyer Rental Properties 35 Washington St. Elkton MD 21922 ¤ 19,740.00 ¤ 2,095.00 ¤ 21,835.00 Residential UG01
GRC01 Gwynedd Retirement Community 302 Great Rd. Gwynedd PA 19436 ¤ 34,036.00 ¤ 580.00 ¤ 34,616.00 Residential MR01
HRG01 Halko Restaurant Group 1294 Monument Ave. Media PA 19063 ¤ 10,952.00 ¤ 0.00 ¤ 10,952.00 Commercial RH01
KRC02 Kennett Retirement Community 7 Lakeview Rd. Kennett Square PA 19348 ¤ 44,026.00 ¤ 3,590.80 ¤ 47,616.80 Residential GZ01
MCC02 Mike’s Convenience Stores 17 Briarwood Ln. Camden NJ 08101 ¤ 0.00 ¤ 1,280.75 ¤ 1,280.75 Commercial SL01
MHS01 Mary’s Hoagies and Steaks 346 Magee Ave. Philadelphia PA 19111 ¤ 21,769.00 ¤ 2,890.60 ¤ 24,659.60 Commercial RH01
MIG01 MidAtlantic Insurance Group 1702 Hunter Ave. Pennsauken NJ 08109 ¤ 13,097.00 ¤ 2,450.00 ¤ 15,547.00 Corporate BR01
NCA03 Norristown Condominium Association 29 Newtown Circle Norristown PA 19401 ¤ 22,697.00 ¤ 3,480.45 ¤ 26,177.45 Residential ER01
OTR03 Old Town Rentals 682 Hickory Way Philadelphia PA 19147 ¤ 15,345.00 ¤ 2,875.50 ¤ 18,220.50 Residential RH01
RDS03 Roehr Department Stores 913 Main St. Wilmington DE 19810 ¤ 34,546.00 ¤ 0.00 ¤ 34,546.00 Commercial KS01
SCC03 Springfield College System 41 Carver Hill Rd. Springfield PA 19064 ¤ 2,500.25 ¤ 0.00 ¤ 2,500.25 Corporate MR01
SRG01 Starvos Restaurant Group 3 Pleasant St. Malvern PA 19355 ¤ 42,561.00 ¤ 12,040.00 ¤ 54,601.00 Commercial GZ01
VMA01 Villa Maria Apartments 200 Queen Rd. Malvern PA 19355 ¤ 0.00 ¤ 0.00 ¤ 0.00 Residential GZ01
WBT01 Wilmington Bank & Trust 52 Putnam Rd. Wilmington DE 19806 ¤ 35,640.00 ¤ 9,875.40 ¤ 45,515.40 Corporate KS02
WCM01 West Chester Mall 456 Selwyn Ave. West Chester PA 19380 ¤ 46,540.00 ¤ 13,450.00 ¤ 59,990.00 Commercial RH01
TypeID Description MinRate Deposit
COM Commercial ¤ 1,000.00
CRP Corporate ¤ 2,500.00
GVT Government ¤ 500.00
HOA Home Owners Assn ¤ 500.00
RES Residential ¤ 800.00
ManagerID FirstName LastName Address City State PostalCode PhoneNumber StartDate Salary CPRCert Skills
AM01 Alex Middleton 54 Quarry Ave. Coatesville PA 19320 610-555-6032 8/1/16 ¤ 32,450.90 No Spanish, carpentry
BR01 Belinda Rowan 140 Oak Stump Rd. Pennsauken NJ 08109 856-555-2134 1/11/16 ¤ 36,575.75 No landscaping, AA degree
DH01 Devyn Harlier 51 Abby Rd. Claymont DE 19703 302-555-7767 8/3/15 ¤ 40,320.00 No licensed electrician
ER01 Eric Rogers 103 Main St. Norristown PA 19401 610-555-8295 1/11/16 ¤ 38,767.20 No general contracting
GZ01 Gregory Zolda 7 Moose Head Rd. West Chester PA 19380 610-555-8765 12/7/15 ¤ 42,540.00 No construction management
KS01 Kevin Stasner 143 Oak Stump Rd. Elkton MD 21921 410-555-1230 4/6/15 ¤ 46,575.25 No carpentry, painting
KS02 Kaitlyn Stenhope 9 Shennadoah Ave. Wilmington DE 19810 302-555-0003 4/18/16 ¤ 37,564.20 No electrician, landscaping
MR01 Max Riley 24 Spring Rd Springfield PA 19064 610-555-2323 2/2/15 ¤ 39,875.00 No landscaping
RH01 Rowan Horton 12 Heather Rd. Media PA 19063 610-555-9009 9/21/15 ¤ 41,320.00 No construction management
SL01 Stephanie Lorenz 15 River Rd. Cherry Hill NJ 08002 856-555-3339 6/6/16 ¤ 33,456.75 No road work
UG01 Ursula Grayson 342 Pine St. Fair Hill MD 21922 410-555-8534 3/7/16 ¤ 38,765.30 No general contracting
PropertyID Address City State Services ClientNumber
PCA001 123 Oakley St. Camden NJ MIG01
PCA005 14 Congress St Camden NJ MCC02
PCA006 100 Morgan Ave. Camden NJ MCC02
PCF001 29 Prospect St. Chadds Ford PA CFH01
PCH001 Cherry Hill Mall Cherry Hill NJ CHG05
PCL001 234 Fourth St. Claymont DE CSS01
PCO001 290 Brook Mill Rd. Coatesville PA FBC04
PDA001 230 First St. Darlington MD FRR02
PDW001 123 Major Rd. Downington PA FBC04
PEC001 134 Lincoln Rd. Elkton MD ECC06
PEL001 100 Maryland Ave. Elkton MD FRR02
PKS001 Route 1 Kennett Square PA KRC02
PMA001 102 Main St. Malvern PA SRG01
PME001 23 Chester Blvd. Media PA HRG01
PME005 789 Valley St. Media PA MHS01
PNC001 100 Main St. New Castle DE RDS03
PNE001 100 Second St. Newark DE DSS02
PNR001 29 Newton Circle Norristown PA NCA03
PPE001 1702 Hunter Ave. Pennsauken NJ MIG01
PPH001 346 Bingham St. Philadelphia PA MHS01
PPH002 200 Robbins Ave. Philadelphia PA MHS01
PPH003 1234 Rising Sun Ave. Philadelphia PA MHS01
PPH006 100 Elfreth’s Alley Philadelphia PA OTR03
PPM001 234 Washington St. Plymouth Meeting PA SRG01
PSP001 Springfield Mall Springfield PA HRG01
PWC001 225 Olympia Rd. West Chester PA RDS03
PWC003 West Chester Mall West Chester PA WCM01
PWI001 102 Townsend St. Wilmington DE DSS02
PWI002 Route 202 Wilmington DE DSS02
PWI003 Route 52 Wilmington DE DSS02
PWI005 52 Putnam Rd. Wilmington DE WBT01
State StateName
DE Delaware
MD Maryland
NJ New Jersey
NY New York
PA 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;

What Students Are Saying About Us

.......... Customer ID: 12*** | Rating: ⭐⭐⭐⭐⭐
"Honestly, I was afraid to send my paper to you, but splendidwritings.com proved they are a trustworthy service. My essay was done in less than a day, and I received a brilliant piece. I didn’t even believe it was my essay at first 🙂 Great job, thank you!"

.......... Customer ID: 14***| Rating: ⭐⭐⭐⭐⭐
"The company has some nice prices and good content. I ordered a term paper here and got a very good one. I'll keep ordering from this website."

"Order a Custom Paper on Similar Assignment! No Plagiarism! Enjoy 20% Discount"