Re: report group subtotals incorrect

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 10/04/04 

Date: Mon, 4 Oct 2004 10:39:53 +0800

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"ivan" <ivanhoe@nospam.optonline.net> wrote in message
news:czY7d.28767$kq6.18820314@news4.srv.hcvlny.cv.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));

Relevant Pages

  • Re: Summing certain criteria - Advanced
    ... I need to find a way to sum the total payments that occur within a gap ... payments and total company payments for a given customer. ... How would I go about accurately calculating the first columns totals ...
    (microsoft.public.excel.misc)
  • Re: report subtotals incorrect
    ... you need to remove the payments records and place ... > sub-total on Total Sales.. ... > tehn totals per group. ... I get the totals paid per customer and ...
    (microsoft.public.access.reports)
  • Re: Summing certain criteria - Advanced
    ... "Don Guillett" wrote: ... > payments being made due to a deductible. ... >>> payments and total company payments for a given customer. ... >>> How would I go about accurately calculating the first columns totals ...
    (microsoft.public.excel.misc)
  • Re: create totals based on a field values...
    ... In general an invoice is no more final than ... form or report calculations to display those values ... Payments table. ... >doesn't change therefore the totals don't change... ...
    (microsoft.public.access.queries)
  • Re: Summing certain criteria - Advanced
    ... I need to find a way to sum the total payments that occur within a gap ... I have many different spreadsheets that contain total> customer ... > How would I go about accurately calculating the first columns totals> where ...
    (microsoft.public.excel.misc)