Summing Totals?

[RitaMoloney]

I have four tables called;
tblParishioners:- AccountNo, AccountName
tblYears:- YearID, AccountNo, YearPeriod
tblCollections:- CollectionID, YearID, CollectionType, NoCollections, AmtPerCollection
tblAccDetails:- AccDetailsID, CollectionID, CollDate, Status, Remark, AmountPaid

A person will be paying towards 1 or more CollectionTypes in any given year.

I am trying to create a query to sum up Total Amt Paid, Total Amt Unpaid, Total Due to be paid in any given year for each person.

I can create a query to sum the totals in each collections;
Total Amt Paid = Sum([tblAccDetails.AmountPaid])
TotalDue = [NoCollections]*[AmtPerCollection]
TotalAmtUnpaid = [Total Amt Paid]-[TotalDue]

How do I calculate the sum of these to create totals for the year for each person?

 

[statsman]

You can go about this one of two ways:
1. Create a table for each year. The table structure would be the same but the data would be different (eg. you may have a collection for "Foreign Missions" this year but not last year).

2. All in one table you could have the name of the parisioner followed by the year followed by the types of collection:

PARISHONER YEAR COLLECTION1 COLLECTION2
Joe Smith 2004 350.00 225.00
Joe Smith 2003 350.00 200.00

In your queries you could select the year you want a report for.

Hope this helps

 

[Pat Hartman]

Sorry statsman, wrong in both cases. NEVER use data to create separate tables. You can always use criteria to select data for a particular year for example. NEVER use data to create repeating groups - collection1, collection2


Include the year and account number in your totals queries. You'll need nested queries because you are including summed data from tables related 1-many. The following is something like what you'll need.

query1:
Select tblAccDetails.CollectionID, tblYears.YearPeriod, Sum(tblAcctDetails.AmountPaid) As SumAmtPaid
from tblAcctDetails Inner Join tblYears on tblAcctDetails.CollectionID = tblYears.CollectionID
Group by tblAcctDetails.CollectionID, tblYears.YearPeriod;

query2:
Select tblParishioners.AccountNo, tblParishioners.AccountName, tblYears.YearPeriod, query1.SumAmtPaid, Sum(AmtPerCollection) As SumAmtPerCollection
From tblParishioners Inner Join tblYears on tblParishioners.AccountNo = tblYears.AccountNo, Inner Join tblCollections on tblYears.YearID = tblCollections.YearID, Inner Join query1 on query1.CollectionID = tblCollections.CollectionID AND query1.YearPeriod = tblYears.YearPeriod
Group By tblParishioners.AccountNo, tblParishioners.AccountName, tblYears.YearPeriod, query1.SumAmtPaid;

 

[RitaMoloney]

Hi,

Thanks for your help.
Got it working.

Rita