Allocated funds in one table consume total recieved from another table

[jthoni]

I am hoping someone can point me in the direction where I should start researching this (and that this isn't a hopelessly stupid question).



Last year, I created a database for an anual student fundraiser I manage. I kept track of the students, thier performance in the fund raiser, and the dontations received.

I had the student data in one table, and dontations collected in another table. I then had a relationship between them, and a view that pivoted on the student (i.e. had the student details, and a subform showing all the donations received).

The problem I ran into was that some donors gave money for multiple students (i.e. if there were siblings). As I was using the donor reciepts table to track deposits as well, I only had one entry per check.

What I want to do is to have the donor reciepts have just the deposit details (i.e. name, check#, total amount, etc.). I then want to have a separate table that records "allocated donations" that consumes the total donation. Example:

Students table
================

 
StudentID StudentName 
1..............Smith, Toby
2 .............Smith, Becka

Donations table
============

 
DonationID DonationName DonationAmount
1..............Schmoe, Joe...$100

Allocated donations table
===================

 
AllocationID AllocationStudentID AllocatedDonationID AllocatedAmount
1...............1..........................1..........................$50
1...............2..........................1..........................$50

With this data, I have just the one entry for the deposit (i.e. $100), but I can award credit to two students.

I can do this manually, but I would like to set up a relationship between AllocatedDonations.AllocatedAmount and Donations.DonationAmount such as the total for all AllocatedDonations.AllocatedAmount of a given AllocatedDonationID equals Donations.DonationAmount.

I figure there must be an analog to customers (donors), sales (donations) and sales person (student), but I am unfamiliar with this, and was hoping to find out how to set up this kind of relationship (i.e. were fields are not just linked, but that one is a subset of the other).

Thanks!
~john

P.S. Does anyone know where I can find the tag reference so I know how to put columns in the TABLE tag, or even tabs?

[NoellaG]

Hi,

you can easily write a check routine in the form where you enter the distributed donations. I would suggest to use a main form frmDonations" whith a subform "sfrAllocatedDonations". You can use a DSum function to total the allocated amounts for a location and the compare it with the amount donated in the main form.

succes
NG