Re: SUM on 1 uniques record from primary table

Ok, in this way I see the correct amountpaid for each invoice but the Grand total in the report footer (which is what I'm interested in) should display the total of all amountspaid for all invoices. As you said, now it displays only the last amount paid. To make it more clear this value should be the total income for the year for all invoices. I also tried to use =SUM(txtRunAmount) butit asks me for its value if I execute it. Maybe I should focus into working this externally and adding this total value into a separate temporary table or this is the correct way to do it?



"Marshall Barton" <marshbarton@xxxxxxxxxx> wrote in message news:obq0l3lmpbojdvnn4n77i6ftfr6d2mes1k@xxxxxxxxxx
The =Sum(... text box will always sum the values from every
detail. Because this a useless calculation, you should just
delete this text box.

To debug the running sum, make txtRunAmount visible. It
should display a running total of the invoice amount.

The report footer grand total text box is supposed to
display the running total from the last invoice group
header. You said the grand total is blank and that implies
that you forgot to set the txtRunAmount text box's Control
Source to the AmountPaid field.
--
Marsh
MVP [MS Access]


Andreas Y. wrote:
I made a text box in the InvoiceNo header, named it txtRunAmount and defined
the options you said. Then I assigned the value of grand total in the report
footer as =txtRunAmount. It displays blank. Also the summary of the
InvoiceNo footer with the total amount paid still sums all the instances of
the amount paid so if the customer paid 20 for an invoice with 4 items, it
will display 4x20. Maybe I do something wrong? I also tried to assign
=Sum([AmountPaid]) at the control source of txtRunAmount but it still
displays blank.


"Marshall Barton" wrote
Andreas Y. wrote:

I have 2 related tables which I have used in a query and then in my Total
Income report. In the primary table I have the AmountPaid field which is
the
amount paid by a customer for an invoice. In my report I list all the
invoice items and the AmountPaid is repeated for each record. As a result,
if I SUM the AmountPaid I get a wrong result because the same value is
added
for each item in the invoice. How can I find the sum on the unique value
of
the Amount Paid if this value is in my primary table?

Put another Amount text (named txtRunAmount) box in the
invoice group header and set its RunningSum property to Over
Group.

THen the report footer text box can display the grand total
by using the expression =txtRunAmount

.

Relevant Pages

  • Re: How to "POST" accounting data into underlying tables
    ... charges to be added to an invoice. ... One option would be to put a SetValue macro on the After Update event of ... This assumes that Invoice Number is the name of the combobox, and Amount ...
    (microsoft.public.access.macros)
  • Re: Get Sum Amount from SubForm
    ... Instead of referring to TotInvoiceAmt in your code, ... I have a Mainform (Invoice) with a Subform with details. ... control name 'Amount ' and in the Form footer I have a control TotAmt ... many times I can press the PayButton before going to the ...
    (microsoft.public.access.forms)
  • Wrong amount on claim - CPR ?
    ... Need a small amount of "small claims court" procedure info. ... disputed some of an invoice). ... invoice in dispute the solictors who filed the claim used the wrong ...
    (uk.legal)
  • Re: Multiple Fields in record
    ... store the total invoice amount anywhere. ... detail amounts others will be blank and only a totals amount would be in ... You might like to look at a couple of my favourite web references on ...
    (microsoft.public.access.gettingstarted)
  • Re: a credit on an invoice
    ... and the amount paid will be applied back as a credit in the ... as a credit towards each month's invoice. ...
    (alt.usage.english)
Loading