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

 

[RitaMoloney]

Hi,

Thanks for your help.
Got it working.

Rita