Create a query that will select records for customers

Assignment Help Database Management System
Reference no: EM131319751


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: EM131319751

When would creating an index reduce overall database perform

Indexing can affect the performance of a general-purpose database that has roughly equal numbers of fetches and changes. The decision to add an index can improve the performan

List course along with names of students from database table

List the courses (D-code and C-no), along with the names of the students who are currently taking them. List all the courses (D-code and C-no) that John (i.e., S-Name=''John''

Why big data has become a hot topic in the business world

Read the article Big Data basic concepts and benefits explained. Focus on why this has become a hot topic in the business world. Describe why big data has become a hot topic

Reflect upon the desirable characteristics of a primary key

Reflect upon the desirable characteristics of a primary key (below) that were presented in this week's lecture. (150-300 words) Choose one characteristic and discuss why you

Ways of implementing one-to-one relationships

Describe the difference ways of implementing one-to-one relationships. Assume you are maintaining information on offices (office numbers, building, and phone numbers)

Data protection and security

Discuss ways that the IT organization can counter the negative impacts of social networking. Explain how the IT organization can maintain an ethical posture while managing o

Differentiate between the interaction types and styles

Explain the conceptual model employed in the design of these types. Describe the analogies and concepts these monitors expose to users, including the task-domain objects use

Create an er diagram that will facilitate development of cms

For this phase, you are required to create an ER Diagram that will facilitate the development of CMS's company-wide database.It should be created using ER Assistant and incl


Write a Review

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