Sum not Summing! 2

[jeffshex]

This should be fairly straitforward, but I'm having trouble figuring out why it's goofing up.

I have the report stemming from a very simple query.
It's getting two tables and there are no criteria.

All I'm looking to do is put a Sum in the report's footer, so I have the text box's control source as this :

=Sum([Amount])

For some reason the Sum is way more than what it should be.
The field [Amount] shows up in each of the detail's header sections.

Any ideas?

 

[jeffshex]

I see the problem:
The query is gathering the detail records and repeating the [Amount] for each detail from the other table, hence adding up the Amount multiple times.

 

[jeffshex]

So now the question would be, how do I break it out so it only sums up the Amount only one time per person.
The query result looks like this:

 Name    Amount   Payment   Date
------------------------------------
John     $300.00   $50.00    5/16/2006
John     $300.00   $75.00    7/22/2006
John     $300.00   $50.00    8/20/2006

Name and Amount come from the "tblGeneral" table.
Inner Join on the "tblPayments" table.
The Sum on the report is adding up each instace of the Amount so it's saying it should be $900.00 when it should only be $300.00. But because of the 3 payments made, it gets all crazy on me.

I'm open for suggestions as I am stuck.
Thanks!!!

 

[dhookom]

One method is to group by Name and create a text box in the Name group header.
Name: txtAmtRS
Control Source: [Amount]
Running Sum: Over All
Add a text box to the report footer section:
Name: txtWhoCares
Control Source: =txtAmtRS

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jeffshex]

Yeah, that sounds like a good idea.
I ended up using a DSum because it's a small database.
But I will try that way you described.

Thanks!