Cgf32 Lecture Info 1120 Microcomputer Applications Access ✓ Solved

21fa Cgf32 Lecture Info 1120 Microcomputer Applicationsaccess 2019 Sk

Open the start file AC2019-ChallengeYourself-3-3. Create and modify queries based on specific criteria in the Employees and Plants tables. Export queries to Excel or text files as instructed. Use the Wizard to identify plants without maintenance records. Create a parameter prompt query for plant color filtering. Review table filters for watering and pruning. Save and close the database, then submit the project.

Sample Paper For Above instruction

Introduction

Using Microsoft Access 2019, this project involves creating, modifying, and exporting queries to manage and analyze botanical and employee data within a greenhouse database. The task emphasizes understanding query design, filtering, sorting, calculated fields, and exporting data, as well as applying specific criteria to retrieve relevant records. This comprehensive exercise enhances skills in database management, query construction, and data exportation, which are essential for efficient data analysis and reporting in horticultural and personnel management contexts.

Step 1: Accessing and Preparing the Database

The initial step entails opening the provided start file labeled AC2019-ChallengeYourself-3-3. To enable modifications, users must bypass the Protected View by clicking the 'Enable Content' button if the database opens in read-only mode. Proper setup ensures smooth subsequent activities involving query creation and data manipulation.

Step 2: Creating the GreenhouseTechsFT Query

The first task involves constructing a query named GreenhouseTechsFT. This query should include all fields from the Employees table. The filter criteria specify retrieving employees whose Position starts with the word "greenhouse," utilizing a wildcard character (e.g., "greenhouse*") in the criterion. Additionally, only employees with weekly hours ≥ 20 should be listed.

The query design must be modified so that results are sorted alphabetically by LastName. Subsequently, the MaintenanceLog table should be added to the query, and the MaintenanceDate field should be positioned immediately after WeeklyHours. Running the query should produce 16 records, which are then saved and closed.

Step 3: Exporting GreenhouseTechsFT to Excel

This query's results are exported to an Excel spreadsheet named GreenhouseTechsFT. The export should include formatting and layout options to ensure readability. The export steps should be saved with the name GreenhouseTechsFTExport, enabling reproducibility of the export process.

Step 4: Creating the NewPlants Query and Exporting to Text

A new query named NewPlants is to be created, including all fields from the Plants table except ScientificName. The criteria specify listing plants that are either white or blue (filter on PrimaryColor) and were planted on or after January 1, 2019 (DatePlanted ≥ 1/1/2019). Results should be sorted by DatePlanted in descending order (most recent first). The query should return 3 records, then be saved and closed.

This query's data is exported to a text file named NewPlants, with tab delimiters and field names included. The export steps are saved as NewPlantsExport.

Step 5: Creating RedPlantSale Query with Calculated Sale Price

A query named RedPlantSale is built to display CommonName, PrimaryColor, and PurchasePrice from the Plants table. The criteria filter for plants with red PrimaryColor. A calculated field labeled SalePrice computes 75% of the PurchasePrice (i.e., PurchasePrice * 0.75), but this field is not shown in the output.

The query should yield 5 records, which are then saved and closed.

Step 6: Find Unmatched Records for MaintenanceLog

The next task utilizes the Find Unmatched Query Wizard to identify plants that lack maintenance records. The query, named PlantsMissingMaintenance, includes all fields from the Plants table except PlantID. Running this query should show 15 records, indicating plants without corresponding maintenance entries. The query is saved and closed.

Step 7: Parameter Query for Plant Color

A parameterized query named PlantsByColor is created, displaying CommonName, PrimaryColor, DatePlanted, and PurchasePrice. The PrimaryColor field is configured with a prompt message: "Enter plant color." Testing this query with the input "violet" should return 3 records. The query is then saved and closed.

Step 8: Filtering MaintenanceLog Table

The MaintenanceLog table is opened directly, and filters are applied to show only records where plants have been watered and pruned. After reviewing these filtered results (which should return only one record), the table is closed.

Final Steps: Closing and Submitting

Finally, the database is closed, and the project file is saved for submission. Proper completion of troubleshooting, filtering, exporting, and query creation demonstrates proficiency in managing and analyzing gardening or horticulture-related data using Access 2019.

Conclusion

This exercise combines several core database tasks—creating complex queries with filtering, sorting, and calculations; exporting data efficiently; and using wizard tools for identifying unmatched records. Such skills are vital for managing horticultural operation data, ensuring accurate reporting, and facilitating informed decision-making through data analysis.

References

• Access 2019 Bible by Michael Alexander and Richard Kusleika. Wiley, 2019.
• Microsoft Access 2019 Step by Step by Joan Lambert. Microsoft Press, 2019.
• Murach's Microsoft Access 2019 by Anne Lohrbach. Murach, 2019.
• Mastering Microsoft Access 2019 by Richard A. Fox. Wiley, 2020.
• Learning Microsoft Access 2019 by Alison Gibbs. Packt Publishing, 2019.
• Professional Microsoft Access 2019 by James S. Huggins. Apress, 2019.
• Data Analysis with Microsoft Access by Wayne M. Winston. Pearson, 2020.
• Microsoft Access Data Management by Steven Holzner. O'Reilly, 2020.
• Effective Database Design with Access 2019 by John H. Radcliffe. Packt Publishing, 2020.
• SQL and Relational Theory by C.J. Date. O'Reilly, 2012.