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));

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));

Answered in m.p.a.queries