List Each Accounting Year Value at the end of the report

[access2010]

Could I please receive assistance on how to calculate the Accounting Year Totals as a summary at the end of our reports?

Example;
Accounting year Interest Paid
2000 / 01 $169.58
2001 / 02 $61.14
2002 / 03 $8,064.00
2003 / 04 $00.00 as there are no transactions
2004 / 05 $8,000.00
2005 / 06 $15,641.04
======= =========
Total Interest Paid for all years $

Thank you for your suggestion.
Nicole

 

[pbaldy]

I'd use a subreport based on a totals query that has the same criteria as the report but sums by your year field.

 

[access2010]

I'd use a subreport based on a totals query that has the same criteria as the report but sums by your year field.

Thank you Pbaldy for your suggest, which I just tried.

We are using Access 2003 and I tried to create the subreport in the = Report Footer
Using data from the Table Vendor Payables.
I selected fields, Accounting Year and Interest Amount.
The only Accounting Year that appears is 2005 / 06 , but the Value is wrong.

Could you suggest, what I have done wrong

Thank you,
Nicole

 

[pbaldy]

Could you suggest, what I have done wrong

No idea without seeing the SQL.

Try

SELECT VendorPayables.[Accounting Year], Sum(VendorPayables.InterestAmount) AS SumOfInterestAmount
FROM VendorPayables INNER JOIN Vendors ON VendorPayables.[Vendor#] = Vendors.[Vendor#]
WHERE (((VendorPayables.Reference)="LoanInterst"))
GROUP BY VendorPayables.[Accounting Year]
HAVING (((Sum(VendorPayables.InterestAmount))>0));

 

[pbaldy]

I just took the SQL behind the report, made it a totals query, and cut it down to the required fields changing Group By to Sum on the interest field.

 

[MajP]

Something like this.

 

[access2010]

Something like this.

MajP = P.E.R.F.E.C.T.
Thank you for your magic touch.
Nicole

 

[access2010]

I just took the SQL behind the report, made it a totals query, and cut it down to the required fields changing Group By to Sum on the interest field.

 

[access2010]

Thank you Pbaldy for your suggestions.

Nicole

 

[pbaldy]

Thank you Pbaldy for your suggestions.

Nicole

No problem.

 

[access2010]

Something like this.

 

[access2010]

Thank you MajP for your sub report assistance and all of our reports work except the sub report in = Interest_05_VendorOneVendor_R.

Could you, please advise me as to what I have done wrong?
Crystal

 

[pbaldy]

You're calling a function that doesn't exist in Text10:

=fnDbName()

 

[access2010]

You're calling a function that doesn't exist in Text10:

=fnDbName()

Thank you for your note, Pbaldy, but the Text10:, function, report name works in all of our reports.

Our current problem, is why does the Sub Report, only shows ONE Accounting Year's Record in this report, while it shows ALL Accounting Year Records in all of our other reports.

Thank you.
Nicole

 

[pbaldy]

Sorry. It's because you have master/child links in the subreport control of the report where it doesn't work.