167|SQL – Annual Order Reports

Challenges
, , , ,
1

BYU Student Author: @Andrew_Wilson
Reviewers: @Benjamin_Lau, @Millie_K_B
Estimated Time to Solve: 30 Minutes

We provide the solution to this challenge using:

  • SQL

Need a program? Click here.

Overview
At Simple Screenprinting we produce branded apparel for customers and businesses. As businesses order higher quantities of goods, they receive a bulk discount. Some companies choose to purchase large quantities of branded goods and then sell them to their employees as a way of fundraising for charities they are associated with. One such company is called VistaPoint Properties. In our system, we have two accounts for VistaPoint Properties. The first account is for when the corporate office places a bulk order, the second is for their employees to purchase the goods and donate to the fundraiser. At the end of each year, Simple Screenprinting runs a report to identify how much money VistaPoint Properties has raised and sends VistaPoint Properties= a check.

The report you are preparing should contain only the amount that we owe to VistaPoint Properties. You need to figure out how many items were ordered by VistaPoint employees during 2019. For each item that was ordered, we will be paying $5 to VistaPoint Charity. You can distinguish between orders placed by the corporate office and orders placed by employees through the order name or the CustomerID. Orders placed by Employees are placed using the CustomerID “2842”.

Instructions

  1. Download and access the Access Database
  2. Generate a report showing the following information
    a. JT_Num
    b. CustomerID
    c. OrderDate
    d. QtyOrdered
  3. Make sure all rows have all fields filled out
  4. We are only interested in orders placed in 2019
  5. Sort by the JT_Num in descending order

Data Files

Suggestions and Hints
  1. Instead of filtering by order name, it may be easiest to figure out which CustomerID is associated with employee orders and filter based on that
  2. When filtering based on the date, try using #XXXX/XX/XX#

Solution

Here are your first 10 rows of data:
image
There should be 129 rows of data total
Challenge167_Solution.yxmd
Solution Video: Challenge 167|SQL – Annual Order Reports