Posted: December 2nd, 2014

Bay Traders, an international gourmet food distributor

Bay Traders, an international gourmet food distributor

Assignments

Project description
Project
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 employees

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 your name, as well as the field title courtesy. Click Save and then close the 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, Contact Title, Phone, OrderID, LastName, OrderDate, and ShippedDate. Use the

relationships window to determine which tables you need before you begin.
b.Run the query, and then examine the records. Save the query as ShippingEfficiency by clicking Save Object As.
c.Add a calculated field named DaysToShip to calculate the number of days taken to fill each order.
d.Run the query, and then examine the results. Save the query.
e.Add criteria to the field DaysToShip to limit the query results to include any order that took more than 30 days to ship.
f.Run the query, and then Save the query as ShippingEfficiency_30Days+.
g.Add the ProductID and Quantity fields to the Shipping Efficiency query. Sort the query by ascending OrderID. When the sales reps contact these customers, these two

fields will provide useful information about the orders.
h.Run the query and switch to Datasheet view to 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.
i.Save the query as ShippingEfficiency_w_PQ_Info.
j.Close all the 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 plus the Products table. Add the fields OrderID AND OrderDate. Click Totals in the Show/Hide Group;

the Total row for both fields should be Group By.
b.In the third column, add a calculated field: 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.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 query, and then use Save Object As to create a new query named Order Summary 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; there should be 21 countries listed.
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.

PLACE THIS ORDER OR A SIMILAR ORDER WITH US TODAY AND GET AN AMAZING DISCOUNT 🙂