Brilton Madley, a board game manufacturer, hired a new CEO. She asked for your assistance in providing summaries of data that took place before she started with the company. To help her with her strategic planning, you will create queries to perform data analysis. Based on your meeting, you plan on creating four queries. One query will find orders with minor delays. Another query will summarize company revenue and cost by country. A third query will be used to help evaluate payments made by customers on their orders. The final query will calculate the total sales by sales representative title.
|1||Start Access. Open the downloaded Access file named Exp19_Access_Ch03_Cap_Brilton_Madley_Games.accdb.Grader has automatically added your last name to the beginning of the filename.||0|
|2||Create a query using Query Design. From the Customers table, include the fields FirstName, LastName, and Phone (in that order). From the Orders table, include the fields OrderID, OrderDate, and ShippedDate (in that order). Run the query and then examine the records. Save the query as Shipping Efficiency.||5|
|3||Add a calculated field named DaysToShip to calculate the number of days taken to fill each order. (Hint: the expression will include the OrderDate and ShippedDate fields; the results will not contain negative numbers.) Run the query and then examine the results. Save the query.||10|
|4||Add criteria to limit the query results to include any order that took more than 30 days to ship.||5|
|5||Add the Quantity field from the Orders table and the ProductName field from the Products table to the query (in that order). Sort the query by ascending LastName.||5|
|6||Add the caption Days to Ship to the DaysToShip field. Switch to Datasheet view to view the final results. Save and close the query.||5|
|7||Create a query using Query Design and add the Customers, Orders, and Products tables. Add the Country field from the Customers table. Set the field’s Total row to Group By. Save the query as Revenue and Cost by Country.||4|
|8||Add a calculated field in the second column. Name the field TotalRevenue. This field should multiply the quantity ordered (from the Orders table) by the unit price for that item (from the Products table). Format the calculated field as Currency and change the caption to Total Revenue. Change the Total row for the TotalRevenue field to Sum.||10|
|9||Add a calculated field in the third column. Name the field TotalCost. The field should multiple the quantity ordered (from the Orders table) by the unit cost for that item (from the Products table). Format the calculated field as Currency and add a caption of Total Cost. Change the Total row to Sum. Run the query. Save the query. Return to Design view.||11|
|10||Add the OrderDate field and criteria to that field so that only orders made between 7/1/2018 and 12/31/2018 are displayed. Change the Total row to Where. This expression will display only orders that were completed during the second half of 2018. Run the query and view the results. Save and close the query.||10|
|11||Create a copy of the Revenue and Cost by Country query named Customer Payments. Switch to Design view of the new query and remove the Country, TotalCost, and OrderDate fields. Add the FirstName and LastName fields. Move the TotalRevenue field to the end. Rename the TotalRevenue field to OrderTotal. Change the caption to Order Total.||5|
|12||Add a new field using the Expression Builder named SamplePayment. Insert the Pmt function with the following parameters:
• Use .05/12 for the rate argument (5% interest, paid monthly)
• Use the number 12 for the num_periods argument (12 months)
• Use the calculated field OrderTotal for the present_value
• Use 0 for both future_value and type
|13||Change the Total row to Expression for the SamplePayment field. Change the Format for the SamplePayment field to Currency. Run the query and verify the second order(with LastName Sparks) has a sample payment of $26.80. Note: it will display as a negative number. Save and close the query.||10|
|14||Create a copy of the Revenue and Cost by Country query named Revenue by Sales Rep. Switch to Design view of the new query and remove the entire Customers table, the TotalCost, and OrderDate fields. Add the Sales Reps table. Add the Title field from the Sales Reps table and move it to the front of the TotalRevenue field.||5|
|15||Run the query and then examine the summary records; there should be 4 Titles listed. Switch to Design view and change the sort order so that the title with the highest TotalRevenue is first and the title with the lowest TotalRevenue is last. Run the query and verify the results. Save and close the query.||5|
|16||Close all database objects. Close the database and then exit Access. Submit the database as directed.||0|
Why Work with Us
Top Quality and Well-Researched Papers
We ensure that our writers and editors work within the work guidelines and follow all paper instructions to the letter. When placing an order, you choose the academic field and expert level (high school, college, university, or professional). Our team then assigns your paper to a writer with a respective qualification or degree to ensure that you receive quality work.
Professional and Experienced Academic Writers
We employ professional writers with more than two years of experience in academic and business writing. Most of our writers and editors are native English speakers to ensure quality and professional work. We are confident that our team of professional writers can handle all types of business and academic writing work.
Free Unlimited Revisions
We provide a free revision service for all orders. If you feel that our writers missed something, you can request a revision of your paper at no additional cost. When we deliver your work, you have seven days to go through it and request a revision or modification if you are not satisfied. You can also contact our support team directly for any clarifications and queries on revision.
Prompt Delivery and 100% Money-Back-Guarantee
We ensure that all papers are delivered on time. In case we need more time to master your paper requirements and deliver quality work, we may contact you and discuss a deadline extension. If a deadline extension is not feasible, depending on the work and submission deadlines, we guarantee a 100% refund.
Original & Confidential
To ensure that we deliver plagiarism-free work, we use various writing and plagiarism checking tools. Our professional editors' team carefully goes through all work and references used in papers to ensure proper referencing and that original work has been done. We also guarantee confidentiality in all the services that we provide.
24/7 Customer Support
Our support team is available round the clock for any customer queries and communication. We guarantee 24/7 customer support and assistance. Feel free to contact us at any time of day for questions and follow-ups.
Try it now!
How it works?
Follow these simple steps to get your paper done
Place your order
Fill in the order form and provide all details of your assignment.
Proceed with the payment
Choose the payment system that suits you most.
Receive the final file
Once your paper is ready, we will email it to you.
You do not have to spend sleepless nights worrying about your paper. We got you covered. We offer all kinds of writing services.
Regardless of the type of academic paper you need and its urgency, we have writers on call ready to work on your paper. Feel free to choose the field, educational level, and type of paper you want, and we will deliver it at an affordable price. We are here for all your academic and business paper needs. With our round the clock service, we guarantee that you will receive your work on time.
Admission Essays & Business Writing Help
Admission essays are written by students wishing to join a college, graduate school or university, as applications for enrollment. We guarantee quality admission essays and business papers with our professional writing and customer care support services.
We have experienced academic writers and editors who are on standby to make all the necessary changed to your paper at your request. We ensure that your paper is polished and appropriately formatted (APA, Harvard, Chicago/Turabian, MLA formats) before it is delivered.
We provide revision support, where you can request a revision of a delivered paper if you feel that it can be improved or repolished. Your paper is checked by an experienced writer or editor for revamping and improvement upon a revision request. Revision service is free, and you can use it as many times as you wish until you are satisfied with your paper.