SUM on 1 uniques record from primary table

  • Thread starter Thread starter Andreas Y.
  • Start date Start date
A

Andreas Y.

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?
 
Andreas said:
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?
Click to expand...

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
 
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.
 
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]

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.


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
Click to expand...
Click to expand...
 
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 said:
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]

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.


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
Click to expand...
Click to expand...
Click to expand...
 
I haven't managed to do this so, I modified my original query that I used as
a record source and I Summarize the totals there. The only problem is that
in my recordset it will appear once in every row. Is this bad design? If it
is then the fact that the amountPaid appears repeatedly for each item of an
invoice must also be bad design right?



Andreas Y. said:
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 said:
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]

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.


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
Click to expand...
Click to expand...
Click to expand...
 
Andreas said:
I haven't managed to do this so, I modified my original query that I used as
a record source and I Summarize the totals there. The only problem is that
in my recordset it will appear once in every row. Is this bad design? If it
is then the fact that the amountPaid appears repeatedly for each item of an
invoice must also be bad design right?
Click to expand...


Well, I can't say it's bad design, but it makes the record
source query more complex than needed for this issue. The
fact that the total appears on every record is just the way
a totals query works, but it is probably getting in your way
in this case.

I think your biggest problem is my apparent inability to
communicate what is a fairly basic calculation using the
RunningSum property. I'll try describing it another way.

The invoice group header section needs, among your other
stuff, two text boxes:

Name txtAmountPaid
ControlSource AmountPaid
RunningSum No

Name txtRunAmount
ControlSource AmountPaid
RunningSum Over All

The report footer section, then can use a text box to
display the grand total:

Name txtGrandTotal
ControlSource =txtRunAmount
RunningSum No

Note that the detail section stuff is unaffected and the
record source query does not need to calculate anything
related to the grand total.
 
Back
Top