Bay Traders, an international gourmet food distributor, is concerned about shipping delays over the last six months. Review the orders over the past six months and identify any order that was not shipped within 30 days. Each customer that falls within that time frame will be called to inquire about any problems the delay may have caused. In addition, an order summary and an order summary by country will be created.
Database File Setup
Open the food database, use Save As to make a copy of the database, and then use the new database to complete this exercise. You will replace an existing employee's name with your name.
a. Locate and open the file BAY_food.accdb by Microsoft Access.
b. Click the File tab, click Save Database As, and then type Lastname_Firstname.
c. Click Save.
d. Open the Employees table.
e. Replace Rachael Eliza with Eric Lewis, as well as the field "title courtesy". Click Save and then close the table.
f. At the database Tool menu and find/click Relationships. View the relations among the 8 tables and the data fields of each table.
DaysToship Query
You need to create a query to calculate the number of days between the data an order. As you create the query, run the query at several intervals so you can verify that the data looks correct. The result of your work will be a list of orders that took more than three weeks to ship. The salepeople will be calling each customer to see if there was any problem with their order.
a. Create a query using Query Design. Include the fields CompanyName, ContactName, ContactTitle, Phone, OrderID, LastName, OrderDate, and ShippedDate. Use the relationships window to determine which tables you need to get the 8 fields before you begin. Remember, only add the tables you need to the query.
b. Run the query, and then examine the records. Save the query as ShippingEfficiency by clicking Save Object As.
c. Back to the query, switch to Design View by clicking Home menu View Design View.
d. Add a calculated field named DaysToShip at column 9 by typing DaysToShip: ShippedDate -OrderDate, which calculates the number of days taken to fill each order.
e. Run the query, and then examine the results.
f. Save the query ShippingEfficiency again.
g. Switch back to the Design View. Add criteria to the field DaysToShip to limit the query results to include any order that took more than 30 days to ship (type >30 at the criteria row).
h. Run the query, and then use Save Object As to save the query by the name ShippingEfficiency_30Days+ (here you are creating a new query ShippingEfficiency_30Days+).
i. Switch back to the Design View the ShippingEfficiency_30Days+ query. Right Click at any place outside of the 3 tables at the panel showing the tables. Click Show Table at the drop down menu. Add table Order Details to the query.
j. Add the ProductID and Quantity fields. Sort the query by ascending OrderID. When the sales reps contact these customers, these two fields will provide useful information about the orders.
k. Run the query and view the final results. This list will be distributed to the sales reps so they can contact the customers. In design view, add the Sales Rep caption to the LastName field.
l. Then Run and Save the query as ShippingEfficiency_w_PQ_Info.
m. Close all the 3 ShippingEfficiency queries.
Order Summary Query
You need to create an order Summary that will show the total amount of each order in one column and the total discount amount in another column. This query will require four tables: Orders, Order Details, Products, and Customers. Query to determine if employees are following the employee discount policy. You will then determine which employees are following the company guidelines.
a. Create a query using Query Design and add the four tables above. Add the fields OrderID and OrderDate. Click Totals in the Show/Hide Group. Now you should see a new row "Total" at the lower panel. The Total row for both fields should be Group By.
b. Switch to design View. In the third column, add a calculated field by typing "ExtendedAmount: Quantity*UnitPrice". Format the calculated field as Currency. This calculation will calculate the total amount for each order. Change the Total row to Sum.
c. In the fourth column, add a calculated field: DiscountAmount: Quantity*UnitPrice*Discount. Format the calculated field as Currency. This will calculate the total discount for each order. Change the Total row to Sum.
d. Run the query. Save the query as Order Summary. Return to Design view.
e. Enter the expression Between 1/1/2012 And 12/31/2012 in the criteria of OrderDate. Change the Total row to Where. This is expression will display only orders that were created in 2012.
f. Run the query, view the results, and Save the query as Order Summary Year 2012.
g. Back the Design view. Add the Total Dollars caption to the ExtendedAmount field and add the Discount Amt caption to the
DiscountAmount field.
h. Run the query. Save and close the query.
Order Summary by Country Query
You need to create one additional query based on the Order Summary query you created in previous step. This new query will enable you to analyze the orders by country.
a. Select the Order Summary Year 2012 query, and then use Save Object As to create a new query named Order Summary Year 2012 by Country.
b. In Design view of the new query, replace the OrderID filed with the Country field.
c. Run the query, and then examine the summary records.
d. In Design view, change the sort order so that the country with the highest Total Dollars is first, and the country with the lowest Total Dollars is last.
e. Run the query and verify the results.
f. Save and close the query.
g. Now you should see six (6) queries in your database file. Close the database and exit Access.