Homework 6 / Access HW 2 – Unlimited Pickers – 9 Points

Homework #6/Access HW #2– Unlimited Pickers – 9 Points

Unlimited Pickers is a group of workers providing harvesting services to farmers, with various teams traveling to different locations to harvest crops such as strawberries, grapes, watermelons, cantaloupe, oranges, grapefruit, and apples. The organization has seen increasing demand and wants to better manage data by creating a database. They have provided an Excel file with sample data to assist in designing and implementing this database. The tasks include creating database tables with appropriate fields, establishing relationships, labeling the database, and developing specific queries to analyze the data, including first pickup dates, low-volume harvests, total weights per customer, produce value calculations, and total payments for certain crops, with outputs such as reports and tailored queries.

Paper For Above instruction

In this assignment, the primary goal is to design and implement a comprehensive Microsoft Access database for Unlimited Pickers, a harvesting service provider. The task involves creating tables, defining relationships, crafting specific queries, and generating reports that support the organization’s operational and analytical needs. The process begins with constructing the database schema based on the provided data, followed by establishing logical relationships among tables, such as customers, pickups, produce types, and payments.

Initially, the core tables to be created include the Customers table, which captures consumer details such as Customer ID, Last Name, First Name, Address, City, State, Zip Code, and Phone. The Pickups table should record each picking event with fields for Customer ID (to link to Customers), Service Date, and other relevant details. Additional tables likely include Produce, capturing types like grapes, apples, and strawberries, along with associated weights and payment rates. Defining primary keys, such as Customer ID or Pickup ID, ensures unique identification of records, while foreign keys establish relationships among tables, enabling integrated data management.

Following table creation, relationships are established to connect Customers with their pickups and produce harvest records. For example, Customer ID in the Pickups table links to the Customers table, while produce details are linked via Pickup ID. Proper relationship enforcement ensures referential integrity, preventing orphan records and maintaining data consistency.

Once the database structure is established, attention turns to creating specific queries tailored to organizational questions. The first query, July 2011 – First Picking Query, retrieves customers with their first pickup in July 2011, sorting them alphabetically by Business Name. This involves filtering the Service Date field for dates falling within July 2011, then sorting and selecting relevant customer data.

The second query, Low Volume Query, identifies customers who harvested less than 10 pounds of produce for grapes, watermelons, or strawberries. This involves filtering records based on produce type and weight, selecting the Service Date, Customer Number, Business Name, and the weight, sorted ascending by Customer Number.

Another important query, Customer Total Weight Query, calculates total weights of all produce items per customer, aggregating by produce type and total weight. This facilitates understanding the overall volume each customer contributes and is sorted by Business Name for clarity and organization.

The Produce Value Query computes the payment for each harvesting event for grapes and strawberries, including Business Name, Picking Date, Weight, and calculated value (weight multiplied by rate). This enables precise billing and financial analysis related to specific crops.

Finally, the Apple-Watermelon Query and corresponding report detail the total payments for apples and watermelons per customer, broken out by state. This comprehensive report supports financial reconciliation and operational planning, highlighting the revenue generated by these key crops across geographical regions.

These steps—structure design, relationship establishment, and query creation—are foundational to a robust database that improves data accuracy, enhances decision-making, and streamlines operational workflows for Unlimited Pickers. Building these components with careful attention to data integrity and query efficiency will enable the organization to effectively manage their expanding business and optimize harvesting operations.

References

• Microsoft Access 2019 & Office 365: Introductory Concepts. (2020). Microsoft Press.