report group subtotals incorrect

I

ivan

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

Allen Browne

Yes, it you see an invoice listed multiple times in the source query where
there are multiple payments of the invoice, the report will include it
multiple times, and so the invoice total will be inflated.

There are several ways around this. One idea is to create a Group Header for
each invoice. Add another text box to this section. Set its Control Source
to show the invoice total, and set its Running Sum property to yes.
Control Source: =[InvoiceTotal]
Format: Currency
Name: txtCustInvoiceRS
This text box accumlates the total for the customer over their invoices,
and in the customer's Group Footer you can add a text box with ControlSource
of:
=[CustInvoiceRS]

An alternative idea would be to use a subreport for the payments. The main
report is then bound to a query that lists only the invoices, and there is
no duplication of invoices. The subreport can then list the payments, and
you can collect the total payments and use a Running Sum to accumulate the
total progressively for the group.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

ivan said:
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));
Click to expand...
 
I

ivan

Thank you Allen , I can see how that would work. I will give it a try
tomorrow...

One more question, however. In the same example where you have multiple
payments for the same invoice... I would like to look up all sales that
occurred in 2003 so my query OrderDate is set for 2003. BUT ...how would I
also specify that I want only the sales from 2003 that had at least one of
the payments in 2004 (in other words... show me orders for 2003 and the
corresponding payments but if the sale had 6 payments at least one of the
payments had to have been in 2004.


Query is below.



Allen Browne said:
Yes, it you see an invoice listed multiple times in the source query where
there are multiple payments of the invoice, the report will include it
multiple times, and so the invoice total will be inflated.

There are several ways around this. One idea is to create a Group Header for
each invoice. Add another text box to this section. Set its Control Source
to show the invoice total, and set its Running Sum property to yes.
Control Source: =[InvoiceTotal]
Format: Currency
Name: txtCustInvoiceRS
This text box accumlates the total for the customer over their invoices,
and in the customer's Group Footer you can add a text box with ControlSource
of:
=[CustInvoiceRS]

An alternative idea would be to use a subreport for the payments. The main
report is then bound to a query that lists only the invoices, and there is
no duplication of invoices. The subreport can then list the payments, and
you can collect the total payments and use a Running Sum to accumulate the
total progressively for the group.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

ivan said:
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));
Click to expand...
Click to expand...
 
I

ivan

Hi Allen,

Your solution to the original problem worked perfectly... I created an
additional text box to carry forward the totals. Thank you!

But your second reply has me confused. I do have the payment table in the
query and I am using subqueries... But how would Ilimit it to customers who
are still paying in 2004 by using a subquery? What basis would I use to
filter those out?

The query that I am using 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));
 
I

ivan

Hi Allen,

Your solution to the original problem worked perfectly... I created an
additional text box to carry forward the totals. Thank you!

But your second reply has me confused. I do have the payment table in the
query and I am using subqueries... But how would Ilimit it to customers who
are still paying in 2004 by using a subquery? What basis would I use to
filter those out?

The query that I am using 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));
 
A

Allen Browne

Okay: you do have the Payments table in the query, though I though that was
the triggering the problem you had earlier in this thread, in which case the
subquery idea would be better.

If you want to continue with the Payments table in the query, you could try
adding something like this to the WHERE clause:
AND ((Payments.PaymentDate Is Null) OR (Payments.PaymentDate Between
#1/1/2004 And #12/31/2004#))

I'm assuming that you want to include those that have no payment, or those
that had had any of their payments in 2004.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

ivan said:
Hi Allen,

Your solution to the original problem worked perfectly... I created an
additional text box to carry forward the totals. Thank you!

But your second reply has me confused. I do have the payment table in the
query and I am using subqueries... But how would Ilimit it to customers
who
are still paying in 2004 by using a subquery? What basis would I use to
filter those out?

The query that I am using 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));





Allen Browne said:
To limit your query to only those sales that have a payment in the following
year without having the payment table in this query, you will need to use a
subquery.

To get you started, see:
How to create and use subqueries
at:
http://support.microsoft.com/?id=209066
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

report subtotals incorrect 2
Report subtotal errors 0
Adding up row values 4
Joining Multiple Tables 1
Report Date Range Form -- Error 3
Difficult Query; Trying to Show only One Record for only Two Field 1
Trying to summarise orders and payments 1
Calculating a grand total for an unbound control in a report 9

Top