If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
report subtotals incorrect
I hope you can help me with a query and report in which I am getting a wrong
sub-total on Total Sales.. The report is using the following field- =sum([Total_Sales]) in the group footer and getting the wrong answer while [Total_Sales] in the detail is correct . I have an application which records multiple payments made toward a purchase. I am trying to generate a report based on a query and subquerys that gives me the total payments by customer and the amount invoiced and tehn totals per group (company). I get the totals paid per customer and the total invoiced (Total Sales) per customer perfectly BUT when I try to get a total per group- the totals are off. The payments per entire group are correct but the amount invoiced (i.e. Total Sales) is not correct. I think it is miscalculating per group based on the number of payments that each customer made... so it is increasing the number. In other words I think that what is happening is if a custome rmade three payments the report is calculating the total sales three times. The query is below: SELECT DISTINCTROW Customers.CompanyName, [Sales by Customer Subquery].[Total Sales] AS [Total Sales], Sum(CLng([Sales by Customer Subquery].[Total Sales]*[Sales by Customer Subquery].[SalesTaxRate]*100)/100) AS [Sales Tax], Sum([Sales by Customer Subquery].[Total Units]) AS [Total Units], [School Info].SchoolName, Customers.ContactLastName, Customers.ContactFirstName, Payments.PaymentAmount, [Sales by Customer Subquery].OrderDate, Payments.PaymentDate, Sum([Sales by Customer Subquery].FreightCharge) AS [Total Freight] FROM (([School Info] INNER JOIN (Customers INNER JOIN [Sales by Customer Subquery] ON Customers.CustomerID = [Sales by Customer Subquery].CustomerID) ON ([School Info].SchoolID = Customers.CompanyName) AND ([School Info].SchoolID = Customers.CompanyName)) INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) LEFT JOIN Payments ON Orders.OrderID = Payments.OrderID WHERE ((([Sales by Customer Subquery].OrderDate) Between #1/1/2003# And #1/1/2004#)) GROUP BY Customers.CompanyName, [Sales by Customer Subquery].[Total Sales], [School Info].SchoolName, Customers.ContactLastName, Customers.ContactFirstName, Payments.PaymentAmount, [Sales by Customer Subquery].OrderDate, Payments.PaymentDate HAVING (((Payments.PaymentAmount)0)); |
#2
|
|||
|
|||
If I understand correctly, you need to remove the payments records and place
them in a subreport. -- Duane Hookom MS Access MVP "ivan" wrote in message . net... I hope you can help me with a query and report in which I am getting a wrong sub-total on Total Sales.. The report is using the following field- =sum([Total_Sales]) in the group footer and getting the wrong answer while [Total_Sales] in the detail is correct . I have an application which records multiple payments made toward a purchase. I am trying to generate a report based on a query and subquerys that gives me the total payments by customer and the amount invoiced and tehn totals per group (company). I get the totals paid per customer and the total invoiced (Total Sales) per customer perfectly BUT when I try to get a total per group- the totals are off. The payments per entire group are correct but the amount invoiced (i.e. Total Sales) is not correct. I think it is miscalculating per group based on the number of payments that each customer made... so it is increasing the number. In other words I think that what is happening is if a custome rmade three payments the report is calculating the total sales three times. The query is below: SELECT DISTINCTROW Customers.CompanyName, [Sales by Customer Subquery].[Total Sales] AS [Total Sales], Sum(CLng([Sales by Customer Subquery].[Total Sales]*[Sales by Customer Subquery].[SalesTaxRate]*100)/100) AS [Sales Tax], Sum([Sales by Customer Subquery].[Total Units]) AS [Total Units], [School Info].SchoolName, Customers.ContactLastName, Customers.ContactFirstName, Payments.PaymentAmount, [Sales by Customer Subquery].OrderDate, Payments.PaymentDate, Sum([Sales by Customer Subquery].FreightCharge) AS [Total Freight] FROM (([School Info] INNER JOIN (Customers INNER JOIN [Sales by Customer Subquery] ON Customers.CustomerID = [Sales by Customer Subquery].CustomerID) ON ([School Info].SchoolID = Customers.CompanyName) AND ([School Info].SchoolID = Customers.CompanyName)) INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) LEFT JOIN Payments ON Orders.OrderID = Payments.OrderID WHERE ((([Sales by Customer Subquery].OrderDate) Between #1/1/2003# And #1/1/2004#)) GROUP BY Customers.CompanyName, [Sales by Customer Subquery].[Total Sales], [School Info].SchoolName, Customers.ContactLastName, Customers.ContactFirstName, Payments.PaymentAmount, [Sales by Customer Subquery].OrderDate, Payments.PaymentDate HAVING (((Payments.PaymentAmount)0)); |
#3
|
|||
|
|||
Answered in m.p.a.queries
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
report group subtotals incorrect | ivan | Running & Setting Up Queries | 6 | October 4th, 2004 01:04 PM |
subtotals doubled if printing report after preview | Glenn | Setting Up & Running Reports | 3 | August 12th, 2004 09:30 AM |
Label | SRIT | General Discussion | 2 | June 22nd, 2004 09:42 PM |
Group subtotals need to use report totals | NickM | Setting Up & Running Reports | 2 | June 3rd, 2004 10:46 PM |