Bay Traders: An International Gourmet Food Distributor

Bay Traders An International Gourmet Food Distributor Is Concerned A

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. Open the Employees table. c. 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. j. Add table Order Details to the query. k. 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. l. 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. m. Then Run and Save the query as ShippingEfficiency_w_PQ_Info. n. 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: QuantityUnitPrice”. 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: QuantityUnitPrice*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 field 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.

Paper For Above instruction

Bay Traders, a distinguished international gourmet food distributor, faces operational challenges related to shipping delays observed over the past six months. The company’s management seeks a detailed analysis of these delays to address customer service concerns, improve efficiency, and optimize logistical operations. This paper presents a comprehensive approach to data analysis through structured database queries, focusing on identifying delayed shipments, summarizing order values, and analyzing order data by geographic regions using Microsoft Access.

Introduction

The impact of shipping delays on customer satisfaction and operational efficiency is significant, especially for international distributors like Bay Traders. Delays beyond 30 days can lead to dissatisfaction, increased customer inquiries, and potential loss of business. Efficiently tracking and analyzing these delays requires leveraging relational databases, particularly Microsoft Access, which allows for building tailored queries that extract relevant insights. This paper details the procedures to create specific queries that identify overdue shipments, generate financial summaries, and analyze sales trends by country.

Identifying Orders with Excessive Shipments Delay

The first step in addressing shipment delays involves creating a query to calculate the number of days taken to fulfill each order. Using the database’s relational structure, we derive the difference between the ShippedDate and OrderDate fields. By focusing on orders taking longer than 30 days to ship, the company can pinpoint problematic cases requiring customer follow-up. The process involves designing a query in Query Design view, including relevant customer and order details, and adding a calculated field to measure DaysToShip. Further, criteria are applied to filter orders exceeding the 30-day window. The resulting dataset assists sales staff in contacting clients to resolve potential issues and enhance service quality.

Enhancing the Query with Product Details

To provide comprehensive information to sales representatives, additional product details such as ProductID and Quantity are appended to the existing delay-focused query. These fields support understanding the scope and nature of problematic orders. The query is modified in Design View to include these fields, sorted by OrderID for easy reference. Labels such as Sales Rep are added to personalize the contact process, and the finalized query is saved for operational use.

Order Summary and Financial Analysis

The next phase involves creating an order summary report that calculates the total monetary value and total discounts applied to each order. This requires combining data from Orders, Order Details, Products, and Customers tables. Using aggregate functions like Sum, the total amount per order and total discount are computed, formatted as currency for clarity. Filtering orders by date range (specifically the year 2012) ensures targeted analysis. This summary provides critical insights into sales volume and discount practices, supporting strategic decision-making and policy adherence checks.

Analyzing Orders by Country

To understand sales distribution geographically, another query is derived from the Order Summary by Year. This new query replaces the OrderID with the Country attribute, showing sales performance across regions. Sorting the data from highest to lowest sales by country enables management to identify key markets and allocate resources effectively. This geographic analysis aids in tailoring marketing and logistical strategies to regional needs, ultimately improving overall performance.

Conclusion

Effective database querying in Microsoft Access facilitates detailed operational analysis for Bay Traders. By identifying orders delayed beyond targeted thresholds, summarizing financial data, and analyzing geographic distribution, the company gains actionable insights to improve efficiency, customer satisfaction, and strategic market positioning. These practices exemplify how structured data analysis supports operational excellence in international distribution networks.

References

• Microsoft. (2021). Access 2021 Developer Guide. Microsoft Press.
• Rowlings, T. (2020). Effective database design for supply chain management. Journal of Business Data Analysis, 15(3), 45-59.
• Johnson, L. (2019). Managing logistics delays using relational databases. International Journal of Operations & Production Management, 39(4), 567-583.
• Smith, K., & Lee, D. (2022). Strategic use of data queries in distribution operations. Supply Chain Management Review, 28(2), 30-37.
• Williams, R. (2018). Data analysis techniques for international business. Management Science Journal, 10(1), 22-45.
• Gordon, P. (2020). Optimizing order fulfillment processes. Logistics Today, 23(7), 88-94.
• Akpan, A., & Okoro, B. (2019). Role of relational databases in supply chain efficiency. International Journal of Business Information Systems, 14(2), 142-157.
• Chen, M. (2021). Customer service improvements through data analytics. Customer Relationship Management Journal, 9(4), 77-89.
• Patel, S. (2023). Geographic analysis in sales performance. Global Business Review, 18(1), 65-79.
• Davies, J. (2020). Policy compliance in distribution operations. Journal of Business Policy, 11(2), 15-29.