List Each Accounting Year Value at the end of the report (1 Viewer)

access2010

Registered User.
Local time
Today, 06:13
Joined
Dec 26, 2009
Messages
1,019
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
 

Attachments

  • Summary_of_Each_Accounting_Year=21=177.mdb
    416 KB · Views: 351

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:13
Joined
Aug 30, 2003
Messages
36,118
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

Registered User.
Local time
Today, 06:13
Joined
Dec 26, 2009
Messages
1,019
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

Wino Moderator
Staff member
Local time
Today, 06:13
Joined
Aug 30, 2003
Messages
36,118
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

Wino Moderator
Staff member
Local time
Today, 06:13
Joined
Aug 30, 2003
Messages
36,118
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

You've got your good things, and you've got mine.
Local time
Today, 09:13
Joined
May 21, 2018
Messages
8,463
Something like this.
 

Attachments

  • Summary_of_Each_Accounting_Year MajP.mdb
    608 KB · Views: 281

access2010

Registered User.
Local time
Today, 06:13
Joined
Dec 26, 2009
Messages
1,019
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
 

Attachments

  • B=Summary_of_Each_Accounting_Year MajP.mdb
    732 KB · Views: 365

pbaldy

Wino Moderator
Staff member
Local time
Today, 06:13
Joined
Aug 30, 2003
Messages
36,118
You're calling a function that doesn't exist in Text10:

=fnDbName()
 

access2010

Registered User.
Local time
Today, 06:13
Joined
Dec 26, 2009
Messages
1,019
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

Wino Moderator
Staff member
Local time
Today, 06:13
Joined
Aug 30, 2003
Messages
36,118
Sorry. It's because you have master/child links in the subreport control of the report where it doesn't work.
 

Users who are viewing this thread

Top Bottom