Skip to Content

Solved: How to fix Northwind Traders Developer 2.2 Template Sales Reports bug in qryrptSalesByProduct query?

Troubleshooting Question

I suspect that there is a bug within the “Monthly Orders by Product” report, specifically in the underlying query called “qryrptSalesByProduct.” This report is designed to display each product from orders within a specified timeframe. However, it seems to be displaying the order total rather than the individual item totals within that order.

To illustrate, if there’s an order with two different items, let’s say one item costs $50 and the other $75, the report currently shows two rows (one for each item). However, each row displays the order total of $125, rather than their respective extended amounts. Consequently, the report’s total displays $250, which is incorrect, as the actual total of the order should be $125.

This discrepancy is causing the “Monthly Orders by Product” report to show significantly higher amounts compared to the “Monthly Orders per Employee” report for the same period.

I’m seeking assistance in identifying and addressing this bug within the report, as it’s impacting the accuracy of the sales data presented in the Northwind Traders Developer 2.2 Template. Any insights or solutions would be greatly appreciated.

Solution: Addressing the Query Issue in NW2.2 Dev Edition

We have a solution to resolve the query issue you’ve identified in the NW2.2 Developer Edition. To fix this, please make the following changes to the query named “qryrptSalesByProduct”:

SELECT Products.ProductName, Products.ProductID, Format([OrderDate],"mmm-yyyy") AS MonthYear, Format([OrderDate],"yyyy-mm") AS MonthYearSort, Format([OrderDate],"q-yyyy") AS QuarterYear, Sum([OrderDetails].[Quantity]*(1-[OrderDetails].[Discount])*[OrderDetails].[UnitPrice]) AS SumOfOrderTotal
FROM Products INNER JOIN (Orders INNER JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID) ON Products.ProductID = OrderDetails.ProductID
WHERE (((Orders.OrderDate) Between [Forms]![frmReports].[txtStartDate] And DateAdd("d",1,[Forms]![frmReports].[txtEndDate])))
GROUP BY Products.ProductName, Products.ProductID, Format([OrderDate],"mmm-yyyy"), Format([OrderDate],"yyyy-mm"), Format([OrderDate],"q-yyyy");

Additionally, we recommend updating the caption for the report named “rptSalesByProductQuarterly” to “Quarterly Orders By Product.” This change will help avoid confusion, especially if both reports are open simultaneously, as the current caption is the same as “rptSalesByProduct.”

By making these adjustments, you should be able to address the query issue and enhance the clarity of your reports in the NW2.2 Developer Edition. If you have any further questions or require additional assistance, please feel free to ask.

Alex Lim is a certified IT Technical Support Architect with over 15 years of experience in designing, implementing, and troubleshooting complex IT systems and networks. He has worked for leading IT companies, such as Microsoft, IBM, and Cisco, providing technical support and solutions to clients across various industries and sectors. Alex has a bachelor’s degree in computer science from the National University of Singapore and a master’s degree in information security from the Massachusetts Institute of Technology. He is also the author of several best-selling books on IT technical support, such as The IT Technical Support Handbook and Troubleshooting IT Systems and Networks. Alex lives in Bandar, Johore, Malaysia with his wife and two chilrdren. You can reach him at [email protected] or follow him on Website | Twitter | Facebook

    Ads Blocker Image Powered by Code Help Pro

    Your Support Matters...

    We run an independent site that is committed to delivering valuable content, but it comes with its challenges. Many of our readers use ad blockers, causing our advertising revenue to decline. Unlike some websites, we have not implemented paywalls to restrict access. Your support can make a significant difference. If you find this website useful and choose to support us, it would greatly secure our future. We appreciate your help. If you are currently using an ad blocker, please consider disabling it for our site. Thank you for your understanding and support.