Access 2019 Challenge Yourself 3.3
October 4th, 2022
- Open the start file AC2019-ChallengeYourself-3-3.
- 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.
- 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.
- 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
- 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.
- 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
- 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.
- 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.
- 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.
- 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.
- Close the database and exit Access.
- Upload and save your project file.
- Submit project for grading.
