Sub report totals

[brownk]

Hello, i'm having some problems displaying totals on my main report, from my sub report. I have a sub report named LnAmount, which has three fields. amount, SumOfFeeAmount, and newMoney. In the report footer i have a text box for each set to invisible. =Sum([amount]), named Amount1, =Sum([SumOfFeeAmount]), named fees, and =Sum([newMoney]), named New Money.
On my main report i have in the footer =[LnAmount].[Report].[Amount1]. It seems to work, except it is only displaying the last entry on the main report instead of the totals. Any help would be appreciated!

[June7]

Try referencing the subreport through the subreport container control.

=Reports!mainreportname.subreportcontainername.Rep ort.Amount1

[brownk]

Thanks, i tried that an i am getting the same results.

[June7]

I will have to review the report. If you want to provide db for analysis, follow instructions at bottom of my post.

[brownk]

I would but 90% of the data on this report is confidential, this is coming from a financial institution. Would you like some screen shots?

[June7]

I doubt screen shots would help me analyze issue but post whatever you want. Structure is the issue, not data. You could copy db and delete data (some dummy records to test with would be nice).

[brownk]

Okay so i sort of got it to work with DSum except its giving me the entire totals from all the entries on the sub report. I forgot to mention the subreport lists ALL amounts but my main report is only calling for amounts that meet a certain criteria. The main report is not displaying the totals from the amounts listed on the main report, its a grand total of every single dollar amount in our system.

[June7]

Yes, the DSum would need the same criteria as used by the report.

[brownk]

=DSum("[amount]","qry_LnAmount") - =DSum("[SumOffeeAmount]","qry_LnAmount") - =DSum("[newMoney]","qry_LnAmount") is what i have so far in the footer of my main report. qry_LnAmount being the subreport. Does something else need to go in each expression, or do i need to change something on my LnAmount sub report? Sorry I'm pretty confused

[June7]

There can be only one equal sign and that would be at the beginning of the expression.

Every domain aggregate function has a WHERE CONDITION argument which is used to restrict the set of records used. If you are basing the DSum on the same query that the subreport is bound to, then the filter criteria might be the ID from the main report.

The general syntax is:

DSum("fieldname", "data source", "filter criteria")

The filter criteria is like the WHERE clause of an SQL statement. Access Help has more guidelines on using domain aggregate functions.

I just don't know your project well enough to be more specific.