Access 2019 Challenge Yourself 3.3

 

1. Open the start file AC2019-ChallengeYourself-3-3.
2. If the database opens in Protected View, click the Enable Content button in the Message Bar at the top of the database so you can modify it.
3. Create a new query named: GreenhouseTechsFT

   Add all the fields from the Employees table.
   The query should list all employees whose Position begins with the word greenhouse and whose weekly hours are greater than or equal to 20. Hint: Include a wildcard character in the criterion for the Position field.
   Modify the query design so results are sorted alphabetically by last name.
   Add the MaintenanceLog table to this query and include the MaintenanceDate field after the WeeklyHours field.
   Run the query to review the results. There should be 16 records in the results.
   Save and close the query.

4. Export the GreenhouseTechsFT query to an Excel spreadsheet.

   Name the Excel file: GreenhouseTechsFT
   Include formatting and layout.
   Save the export steps with the name: GreenhouseTechsFTExport

5. Create a new query named: NewPlants.

   Add all the fields from the Plants table except ScientificName.
   The query should list all white or blue colored plants whose DatePlanted is greater than or equal to 1/1/2019.
   Modify the query design so results are sorted by values in the DatePlanted field with the newest plants listed first.
   Run the query to review the results. There should be three records in the results.
   Save and close the query.

6. Export the NewPlants query to a text file.

   Name the text file: NewPlants
   Use Tab as the delimiter.
   Include the field names in the first row.
   Save the export steps with the name: NewPlantsExport

   Save Time On Research and Writing

   Hire a Pro to Write You a 100% Plagiarism-Free Paper.

   Get My Paper
7. Create a new query named: RedPlantSale

   Add the following fields from the Plants table to the query: CommonName, PrimaryColor, PurchasePrice
   Select only those plants with a red color, but don’t show this field in the query results.
   Add a calculated field that displays a sale price that is 75 percent of the purchase price. Hint: Use an expression that calculates the value of the PurchasePrice field multiplied by 0.75. Use the name SalePrice for the new field.
   Run the query to review the results. There should be five records in the results.
   Save and close the query.

8. Use the Find Unmatched Query Wizard to create a new query that identifies the plants that have no entry in the MaintenanceLog.

   Include all fields from the Plants table except the PlantID.
   Name this query: PlantsMissingMaintenance
   Review the query results. There should be 15 records in the results.
   Close the query.

9. Create a new parameter query named: PlantsByColor

   Add the following fields from the Plants table to the query: CommonName, PrimaryColor, DatePlanted, PurchasePrice
   Configure the PrimaryColor field so the user is prompted to enter the primary plant color with this message: Enter plant color
   Test the query using the color violet. There should be three records in the results.
   Save and close the query.

10. Open the MaintenanceLog table. Apply a filter that shows only those plants that have been watered and pruned. Close the table. There should be one record in the results.
11. Close the database and exit Access.
12. Upload and save your project file.
13. Submit project for grading.

ID

1

7

1

2-

55-

4

20

0537

1-555-

45

Greenhouse Tech 2 20

30

20

Greenhouse Tech 1

350

	Employee	LastName	FirstName	MobilePhone	Position	WeeklyHours
	1	7	8	2	4	Miller	Gary					20	5	12	3		Greenhouse Tech 2
	23	6	9	Singleton	Jasmin		30	10
							312693	14	Rojas	Paola	301-555-2430		Greenhouse Tech 1
							59267312	Morber	Tracy	703-555-8899	Supervisor
68023812	Barnes	Dale	202-555-4455	35
778	13	Russell	George	703-555-4456	Greenhouse Tech 3		15

Employee

1

782741

false

2

/19

true true true

3 9/18/19 31269314

true true false

4 9/18/19 31269314

true true false

5 9/18/19 31269314 ASTU false true false
6 9/18/19 31269314

false true true

7

/19

59267312

true false false

8 9/21/19 59267312

true false false

9 9/21/19 59267312 RUHI2 false true true
10 9/21/19 59267312

false true false

11 9/21/19 59267312

false true false

12

/19

31269314 ALFA2 true false false

13 9/22/19 31269314 ASTU false true false
14 9/22/19 31269314 RUHI2 true true false
15

59267312 HEDE4 false true false

82741

true true false

17 9/25/19

ALFA2 false true false

18 9/25/19 11782741 MOPU false true true
19 9/25/19 31269314

false true true

20 9/25/19 31269314

true false false

21

11782741

true true false

22

11782741

false true false

23 9/30/19 59267312 ALFA2 true true false

MaintenanceID	MaintenanceDate	Plant	Watered	Inspected	Pruned
9/15/	19		11					ALFA2																																		false																																			true
				9/	18			31269314				ASTU
			RUHI2
	AMTA2
		MOPU
				9/	21		YUFI
		HEDE4
	COGL2
	AGPU5
		9/	22
9/21/18
16					9/25/19	1	17		RHVI
			11782741
	RHCA7
	PECO4
9/26/19		MOFI
	9/30/19		COER

AGPU5

ALFA2

colicroot

White

AMTA2

¤ 7.50

Violet

ASTU

White

Blue

¤ 6.25

COER

Blue 5/5/18

COGL2

Yellow

3/31/15 ¤ 3.15

Red 3/31/15

Violet

¤ 4.50

HEDE4

Yellow

Red 4/20/16

White

¤ 8.25

Red 4/30/19

Blue 4/30/19

MOFI

Pink 4/30/19 ¤ 3.50

MOPU

White 4/20/16

PECO4

White 5/11/16

Pink

Pink 3/31/15

RHCA7

White 4/10/16

RHVI

Pink 4/8/17

RUHI2

Yellow 4/20/16 ¤ 3.15

Red 4/8/17

YUFI

White 5/5/18

PlantID	CommonName	ScientificName	PrimaryColor	DatePlanted	PurchasePrice
Gerardia	Agalinis purpurea			Violet	4/11/19		¤ 4.50
							White	Aletris farinosa			4/8/17		¤ 7.50
Eastern bluestar	Amsonia tabernaemontana				Blue	5/1/19
ANBE	Wind-flower	Anemone berlandieri		4/10/16	¤ 4.00
AQCA	Eastern red columbine	Aquilegia canadensis					Red	3/28/13		¤ 8.25
Butterflyweed	Asclepias tuberosa	Orange				3/31/15	¤ 11.50
BAAL	White wild indigo	Baptisia alba	4/15/07		¤ 6.25
BAAU	Blue wild indigo	Baptisia australis			5/5/18
Widow’s tears	Commelina erecta	¤ 4.95
Coastal tickseed	Coreopsis gladiata				Yellow				4/20/16			¤ 3.15
COLA5	Lanceleaf tickseed	Coreopsis lanceolata		5/11/16	¤ 5.25
CONU5	Georgia tickseed	Coreopsis nudata					Pink
GAPU	Firewheel	Gaillardia pulchella	¤ 5.50
GEMA	Spotted geranium	Geranium maculatum	9/15/15
Beach sunflower	Helianthus debilis	4/18/16		¤ 3.50
IPRU2	Standing cypress	Ipomopsis rubra	¤ 6.50
KOVI	Seashore mallow	Kosteletzkya virginica				4/30/19
LOCA2	Cardinal flower	Lobelia cardinalis	¤ 9.50
LUPE3	Wild lupine	Lupinus perennis	¤ 5.85
Wild bergamot	Monarda fistulosa
Horsemint	Monarda punctata	¤ 4.90
Wild foxglove	Penstemon cobaea	¤ 2.90
PHNI	Trailing phlox	Phlox nivalis	3/29/09	¤ 2.50
RHAL4	Savannah meadowbeauty	Rhexia alifanus	¤ 1.90
Wild azalea	Rhododendron canescens	¤ 10.50
Handsome Harry	Rhexia virginica	¤ 6.80
Black-eyed Susan	Rudbeckia hirta
SACO5	Scarlet sage	Salvia coccinea	¤ 8.20
Adam’s needle	Yucca filamentosa	¤ 10.90

SELECT Employees.[EmployeeID], Employees.[LastName], Employees.[FirstName], Employees.[MobilePhone], Employees.[Position], Employees.[WeeklyHours]
FROM Employees;