Create a query that will select records for customers

Assignment Help Database Management System
Reference no: EM131322955

Unlimited Pickers is a group of workers who have joined together to provide harvesting services to farmers who need to have their crops brought in. The organization has many teams of workers who travel from location to location picking or harvesting different crops, such as strawberries, grapes, watermelons, cantaloupe, oranges, grapefruit, and apples, among others. Unlimited Pickers has been in business for a few years, but has seen demand skyrocket in recent months (cf. recent legislation in Alabama to reduce the number of undocumented foreign workers). (All workers for Unlimited Pickers are either U.S. citizens or are legally authorized to work in the U.S.; Unlimited Pickers tries to help their workers complete the legal work-permit process.)

Customers, i.e., the farms who want to hire Unlimited Pickers teams, pay a small fee to the organization to reserve one or more teams of pickers. The workers are paid based on the quantity of the produce that they harvest, typically by the pound. (Note that some customers are large organizations, while others are quite small in the amount of produce grown.)

Unlimited Pickers would like for you to assist with the creation of a database program to help better manage their data and will include several queries. Some test data has been pulled from their paper-based business records and placed into a spreadsheet for you to use in designing and building the database. (The spreadsheet file is: HW 6 - Unlimited Pickers.)

Specifically they need for you to do the following:

1. Create the database, including loading the data from the spreadsheet into Access and establishing the Relationships. Label the database with your last name.

2. Create a query that will select records for customers who had their first pickup in July 2011. Sort into alphabetical order by Business Name. Save the query as July 2011 - First Picking Query.

3. Create a query that will select customers who had less than 10 pounds of produce picked for Grapes, Watermelons, or Strawberries. The query should include the Service Date, Customer Number, Business Name, Street, City, State, Zip, and the Weight fields from those attributes. Sort into ascending order by Customer Number. Save the query as Low Volume Query.

4. Create a query to determine the total weights of all produce picked for each customer (by type and total). This should include all produce for a customer. Sort by Business Name. Save the query as Customer Total Weight Query.

5. Create a query to calculate the payment for each picking for grapes and strawberries. Include the Business Name, Picking Date (ascending), and the Weight and value for both types of produce. Save the query as Produce Value Query.

6. Create a query and a report to calculate the total payment for apples and watermelon for each customer. Include the Business Name (ascending) and the payments. In the report, break it out by state.

Reference no: EM131322955

Questions Cloud

Draw an entity relationship diagram for flapper dumpling : Draw an entity relationship diagram for Flapper Dumpling
Discuss the possible threats to internal validity : Discuss the possible threats to internal validity, as well as the ways in which the design could be patched up to provide more conclusive evidence.
What is waste paper ash : What is Waste Paper Ash - Is a waste material collected from the Paper Industry and How is it generated?
Appreciative inquiry process : Suppose your supervisor has asked you to lead an Appreciative Inquiry process and as a first step you need to conduct some interviews with your coworkers. Do a search on "Appreciative Inquiry Questions" or "Appreciative Inquiry Interview Questions..
Create a query that will select records for customers : Create a query that will select customers who had less than 10 pounds of produce picked for Grapes, Watermelons, or Strawberries. The query should include the Service Date, Customer Number, Business Name, Street, City, State, Zip, and the Weight ..
Compare and contrast parenting infants and babies : Compare and contrast parenting infants and babies, parenting children, parenting teenagers, and parenting adults. Explain how parenting demands vary across time and how social factors outside of the immediate family
Explain what sociologist howard s becker meant : Explain what sociologist Howard S. Becker meant when he said, "it is not the act itself, but the reaction to the act, that make something deviant."
In analyzing the data from an experiment : In analyzing the data from an experiment, why is it not sufficient simply to examine the condition means to see whether they differ?
Discuss the key storage and retrieval techniques : From the first e-Activity, evaluate the efficiency, speed, and accuracy of the storage and retrieval techniques that two (2) search engine organizations currently use. Imagine that the Chief Operating Officer (COO) of a new search engine organizat..

Reviews

Write a Review

Database Management System Questions & Answers

  Knowledge and data warehousing

Design a dimensional model for analysing Purchases for Adventure Works Cycles and implement it as cubes using SQL Server Analysis Services. The AdventureWorks OLTP sample database is the data source for you BI analysis.

  Design a database schema

Design a Database schema

  Entity-relationship diagram

Create an entity-relationship diagram and design accompanying table layout using sound relational modeling practices and concepts.

  Implement a database of courses and students for a school

Implement a database of courses and students for a school.

  Prepare the e-r diagram for the movie database

Energy in the home, personal energy use and home energy efficiency and Efficient use of ‘waste' heat and renewable heat sources

  Design relation schemas for the entire database

Design relation schemas for the entire database.

  Prepare the relational schema for database

Prepare the relational schema for database

  Data modeling and normalization

Data Modeling and Normalization

  Use cases perform a requirements analysis for the case study

Use Cases Perform a requirements analysis for the Case Study

  Knowledge and data warehousing

Knowledge and Data Warehousing

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  Practice on topic of normalization

Practice on topic of Normalization

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd