Sum field in report footer not working right

  • Thread starter Thread starter Samantha
  • Start date Start date
S

Samantha

I have created a database to track invoices and payments.
It sounds very much like the database in questions
by "Manish" posted on under subject: "Totals (Do or Die
Situation)". I'm having the same problem as he did: the
sum in the report footer isn't working correctly. I have
realized why but don't know how to fix it.

There are two tables: invoices and payments. They are
joined by field InvoiceNumber. I have also created a query
based on these two tables. The problem seems to be derived
from there being more than one payment against a single
invoice.

When the query is generated, it shows the invoice #, date,
amount, payment date, and payment amount as follows

InvNo InvDate InvAmount PayDate PayAmount
1 1/1/04 5000.00 2/1/04 3000.00
1 1/1/04 5000.00 2/1/04 1000.00
1 1/1/04 5000.00 2/1/04 1000.00

Even though there was actually only one Invoice #1 in the
total amount of 5000.00, it lists it for each payment
transaction. The report only shows the invoice once then
the separate payments, however, the total for the invoices
in the report footer is including the the invoice amount
of $5000 three times instead of once.

I've tried several things at this point as well as the
suggestions replied to Manish's post but haven't had any
luck. It also appears that his problem wasn't resolved
either. Please help!

Thanks, Samantha
 
If you have only one invoice in the report then don't SUM() the InvAmount
field. Just bind a text box directly to [InvAmount].
If you have more than one invoice, do you want to total all invoice amounts
for the entire report? If so, you must group by Invoice and add a header for
Invoice. In the Invoice Header, add a text box:
Name: txtInvAmt
Control Source: [InvoiceAmt]
Running Sum: Over All
ForeColor: (set to red so you can see it clearly)

Then in the report footer, add a text box:
Control Source: =txtInvAmt

If this doesn't work, then tell us what values you are seeing in the red
text box and what value you see in the text box in the report footer.
 
Duane,
As it is now, I have four "dummy" invoices set up in the
database. Some have several payments, some one and some
none at all. The report is set up as follows:

InvoiceNumber Header:
txtInvNo / txtInvDate / txtInvAmt (bound to InvAmt)

Detail:
txtPaymentDate / txtPaymentAmt

InvoiceNumber Footer:
txtSubtotal (this equals InvAmt-Sum(PaymentAmount) and
works correctly)

Report Footer:
Total Invoiced / Total Received / Outstanding Balance

Thus, the report looks as follows:
InvNo Date InvAmount PaymentDate PaymentAmt
1 1/1/04 5000.00
2/1/04 3000.00
3/1/04 1000.00
4/1/04 1000.00
Subtotal 0.00

2 1/15/04 1000.00 payments... etc.

TotalInv TotalPayments O/S Balance

When I changed RunningSum to Overall for txtInvAmt, The
invoice amount for Inv# 2 becomes 6000.00 because it's
including invoice# 1. I also changed the TotalInv to =
txtInvAmt. That only displays the amount shown under
InvAmt colum for the last invoice on the report. I changed
it the RunningSum to Overall and Over Group but it didn't
do anything. I also tried a few combinations of RunningSum
options between txtInvAmt and txtTotalInv but nothing
worked.

Sorry if this is confusing at all. Thanks for your help
and any other suggestions would be helpful. I'm so close
to being able to finish this....
Samantha
 
"The invoice amount for Inv# 2 becomes 6000.00 because it's including
invoice# 1" - that's what I would expect and want
"InvAmt colum for the last invoice on the report" - that's also what I would
expect and want. Isn't this the total of all InvAmount? From your records,
this should be 6000.

--
Duane Hookom
MS Access MVP


Samantha said:
Duane,
As it is now, I have four "dummy" invoices set up in the
database. Some have several payments, some one and some
none at all. The report is set up as follows:

InvoiceNumber Header:
txtInvNo / txtInvDate / txtInvAmt (bound to InvAmt)

Detail:
txtPaymentDate / txtPaymentAmt

InvoiceNumber Footer:
txtSubtotal (this equals InvAmt-Sum(PaymentAmount) and
works correctly)

Report Footer:
Total Invoiced / Total Received / Outstanding Balance

Thus, the report looks as follows:
InvNo Date InvAmount PaymentDate PaymentAmt
1 1/1/04 5000.00
2/1/04 3000.00
3/1/04 1000.00
4/1/04 1000.00
Subtotal 0.00

2 1/15/04 1000.00 payments... etc.

TotalInv TotalPayments O/S Balance

When I changed RunningSum to Overall for txtInvAmt, The
invoice amount for Inv# 2 becomes 6000.00 because it's
including invoice# 1. I also changed the TotalInv to =
txtInvAmt. That only displays the amount shown under
InvAmt colum for the last invoice on the report. I changed
it the RunningSum to Overall and Over Group but it didn't
do anything. I also tried a few combinations of RunningSum
options between txtInvAmt and txtTotalInv but nothing
worked.

Sorry if this is confusing at all. Thanks for your help
and any other suggestions would be helpful. I'm so close
to being able to finish this....
Samantha

-----Original Message-----
If you have only one invoice in the report then don't SUM () the InvAmount
field. Just bind a text box directly to [InvAmount].
If you have more than one invoice, do you want to total all invoice amounts
for the entire report? If so, you must group by Invoice and add a header for
Invoice. In the Invoice Header, add a text box:
Name: txtInvAmt
Control Source: [InvoiceAmt]
Running Sum: Over All
ForeColor: (set to red so you can see it clearly)

Then in the report footer, add a text box:
Control Source: =txtInvAmt

If this doesn't work, then tell us what values you are seeing in the red
text box and what value you see in the text box in the report footer.
Click to expand...
Click to expand...
 
Duane,
The purpose of the report is for the user to be able to
review the total amount outstanding as well as the
balances due on individual invoices in order to pursue
them. Thus, having the next invoice show as a cumulative
figure is not desired. I guess if there's no other way to
fix this then I could have two InvAmt columns - one that
will show the individual invoice and one that will show a
running sum which the total in the footer would also be
based on.
Samantha
-----Original Message-----
"The invoice amount for Inv# 2 becomes 6000.00 because it's including
invoice# 1" - that's what I would expect and want
"InvAmt colum for the last invoice on the report" - that's also what I would
expect and want. Isn't this the total of all InvAmount? From your records,
this should be 6000.

--
Duane Hookom
MS Access MVP


Samantha said:
Duane,
As it is now, I have four "dummy" invoices set up in the
database. Some have several payments, some one and some
none at all. The report is set up as follows:

InvoiceNumber Header:
txtInvNo / txtInvDate / txtInvAmt (bound to InvAmt)

Detail:
txtPaymentDate / txtPaymentAmt

InvoiceNumber Footer:
txtSubtotal (this equals InvAmt-Sum(PaymentAmount) and
works correctly)

Report Footer:
Total Invoiced / Total Received / Outstanding Balance

Thus, the report looks as follows:
InvNo Date InvAmount PaymentDate PaymentAmt
1 1/1/04 5000.00
2/1/04 3000.00
3/1/04 1000.00
4/1/04 1000.00
Subtotal 0.00

2 1/15/04 1000.00 payments... etc.

TotalInv TotalPayments O/S Balance

When I changed RunningSum to Overall for txtInvAmt, The
invoice amount for Inv# 2 becomes 6000.00 because it's
including invoice# 1. I also changed the TotalInv to =
txtInvAmt. That only displays the amount shown under
InvAmt colum for the last invoice on the report. I changed
it the RunningSum to Overall and Over Group but it didn't
do anything. I also tried a few combinations of RunningSum
options between txtInvAmt and txtTotalInv but nothing
worked.

Sorry if this is confusing at all. Thanks for your help
and any other suggestions would be helpful. I'm so close
to being able to finish this....
Samantha

-----Original Message-----
If you have only one invoice in the report then don't
Click to expand...
SUM
() the InvAmount
field. Just bind a text box directly to [InvAmount].
If you have more than one invoice, do you want to total all invoice amounts
for the entire report? If so, you must group by Invoice and add a header for
Invoice. In the Invoice Header, add a text box:
Name: txtInvAmt
Control Source: [InvoiceAmt]
Running Sum: Over All
ForeColor: (set to red so you can see it clearly)

Then in the report footer, add a text box:
Control Source: =txtInvAmt

If this doesn't work, then tell us what values you are seeing in the red
text box and what value you see in the text box in the report footer.
Click to expand...
Click to expand...


.
Click to expand...
 
Don't show the accumulated InvAmount. It has no use other than
troubleshooting except in the report footer. Continue to use a text box like
you had before that balances the individual invoice. From your previous
email:
"txtSubtotal (this equals InvAmt-Sum(PaymentAmount) and works correctly)"


--
Duane Hookom
MS Access MVP
--

Duane,
The purpose of the report is for the user to be able to
review the total amount outstanding as well as the
balances due on individual invoices in order to pursue
them. Thus, having the next invoice show as a cumulative
figure is not desired. I guess if there's no other way to
fix this then I could have two InvAmt columns - one that
will show the individual invoice and one that will show a
running sum which the total in the footer would also be
based on.
Samantha
-----Original Message-----
"The invoice amount for Inv# 2 becomes 6000.00 because it's including
invoice# 1" - that's what I would expect and want
"InvAmt colum for the last invoice on the report" - that's also what I would
expect and want. Isn't this the total of all InvAmount? From your records,
this should be 6000.

--
Duane Hookom
MS Access MVP


Samantha said:
Duane,
As it is now, I have four "dummy" invoices set up in the
database. Some have several payments, some one and some
none at all. The report is set up as follows:

InvoiceNumber Header:
txtInvNo / txtInvDate / txtInvAmt (bound to InvAmt)

Detail:
txtPaymentDate / txtPaymentAmt

InvoiceNumber Footer:
txtSubtotal (this equals InvAmt-Sum(PaymentAmount) and
works correctly)

Report Footer:
Total Invoiced / Total Received / Outstanding Balance

Thus, the report looks as follows:
InvNo Date InvAmount PaymentDate PaymentAmt
1 1/1/04 5000.00
2/1/04 3000.00
3/1/04 1000.00
4/1/04 1000.00
Subtotal 0.00

2 1/15/04 1000.00 payments... etc.

TotalInv TotalPayments O/S Balance

When I changed RunningSum to Overall for txtInvAmt, The
invoice amount for Inv# 2 becomes 6000.00 because it's
including invoice# 1. I also changed the TotalInv to =
txtInvAmt. That only displays the amount shown under
InvAmt colum for the last invoice on the report. I changed
it the RunningSum to Overall and Over Group but it didn't
do anything. I also tried a few combinations of RunningSum
options between txtInvAmt and txtTotalInv but nothing
worked.

Sorry if this is confusing at all. Thanks for your help
and any other suggestions would be helpful. I'm so close
to being able to finish this....
Samantha


-----Original Message-----
If you have only one invoice in the report then don't SUM
() the InvAmount
field. Just bind a text box directly to [InvAmount].
If you have more than one invoice, do you want to total
all invoice amounts
for the entire report? If so, you must group by Invoice
and add a header for
Invoice. In the Invoice Header, add a text box:
Name: txtInvAmt
Control Source: [InvoiceAmt]
Running Sum: Over All
ForeColor: (set to red so you can see it clearly)

Then in the report footer, add a text box:
Control Source: =txtInvAmt

If this doesn't work, then tell us what values you are
seeing in the red
text box and what value you see in the text box in the
report footer.
Click to expand...


.
Click to expand...
Click to expand...
 
Duane,
I have done as you have below and it works great! Thanks
so much for your help.
Samantha
-----Original Message-----
Don't show the accumulated InvAmount. It has no use other than
troubleshooting except in the report footer. Continue to use a text box like
you had before that balances the individual invoice. From your previous
email:
"txtSubtotal (this equals InvAmt-Sum(PaymentAmount) and works correctly)"


--
Duane Hookom
MS Access MVP
--

Duane,
The purpose of the report is for the user to be able to
review the total amount outstanding as well as the
balances due on individual invoices in order to pursue
them. Thus, having the next invoice show as a cumulative
figure is not desired. I guess if there's no other way to
fix this then I could have two InvAmt columns - one that
will show the individual invoice and one that will show a
running sum which the total in the footer would also be
based on.
Samantha
-----Original Message-----
"The invoice amount for Inv# 2 becomes 6000.00 because it's including
invoice# 1" - that's what I would expect and want
"InvAmt colum for the last invoice on the report" - that's also what I would
expect and want. Isn't this the total of all InvAmount? From your records,
this should be 6000.

--
Duane Hookom
MS Access MVP


Duane,
As it is now, I have four "dummy" invoices set up in the
database. Some have several payments, some one and some
none at all. The report is set up as follows:

InvoiceNumber Header:
txtInvNo / txtInvDate / txtInvAmt (bound to InvAmt)

Detail:
txtPaymentDate / txtPaymentAmt

InvoiceNumber Footer:
txtSubtotal (this equals InvAmt-Sum(PaymentAmount) and
works correctly)

Report Footer:
Total Invoiced / Total Received / Outstanding Balance

Thus, the report looks as follows:
InvNo Date InvAmount PaymentDate PaymentAmt
1 1/1/04 5000.00
2/1/04 3000.00
3/1/04 1000.00
4/1/04 1000.00
Subtotal 0.00

2 1/15/04 1000.00 payments... etc.

TotalInv TotalPayments O/S Balance

When I changed RunningSum to Overall for txtInvAmt, The
invoice amount for Inv# 2 becomes 6000.00 because it's
including invoice# 1. I also changed the TotalInv to =
txtInvAmt. That only displays the amount shown under
InvAmt colum for the last invoice on the report. I changed
it the RunningSum to Overall and Over Group but it didn't
do anything. I also tried a few combinations of RunningSum
options between txtInvAmt and txtTotalInv but nothing
worked.

Sorry if this is confusing at all. Thanks for your help
and any other suggestions would be helpful. I'm so close
to being able to finish this....
Samantha


-----Original Message-----
If you have only one invoice in the report then
Click to expand...
don't
SUM
() the InvAmount
field. Just bind a text box directly to [InvAmount].
If you have more than one invoice, do you want to total
all invoice amounts
for the entire report? If so, you must group by Invoice
and add a header for
Invoice. In the Invoice Header, add a text box:
Name: txtInvAmt
Control Source: [InvoiceAmt]
Running Sum: Over All
ForeColor: (set to red so you can see it clearly)

Then in the report footer, add a text box:
Control Source: =txtInvAmt

If this doesn't work, then tell us what values you are
seeing in the red
text box and what value you see in the text box in the
report footer.

--
Duane Hookom
MS Access MVP
--




.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top