diferentiated running subtotals

[Prince -e-PAL]

I am setting up a relational database for a school special ed budget. One database has student demographics, a second database has info on out-of-district schools, etc. My "Main" database is used for budget tracking. In that main database, I enter the student number (bringing demo info with it), the out of district school number (again bringing pertinent info over) and the monthly payment. As students are from different schools within the district, the payments are pulled from different accounts.

 

Bottom line is that I need a running total for each account.

 

To make this "user friendly", so that when the student's school is identified from the student database, the monthly payment can only be withdrawn from the correct school (avoid user error)

 

I have found the running total field function but it appears to only work within the active database and not the related databases.

 

Is there a way to write a calculation something like"

 

Tommy goes the school 1, his January payment of $500 is withdrawn from the school 1 budget and the running total reflects with withdrawal without the user being require to differentiate between schools

 

 

Perhaps

 

if [school number] = "school 1", [running total] = [school 1 budget]-[payment]

 

 

Any suggestions would be greatly apprecated

[touchMe]

This sounds like an accounts system and to my simple mind I would be thinking about having each school setup like a bank account, so that when you pull up the details on any given school it gives a list of 'transactions' (student payments, by name etc.,) and that these are reflected in three right-hand columns of a report

 

Money IN

Money OUT

BALANCE

 

The balance changes cumulatively as transactions are processed

 

Is this what you are after?

[Prince -e-PAL]

Yes that is close.

 

I need to centralize the account control so I can manage all the accounts. I guess I could set up multiple withdrawal field, one for each school, and have my assistant make sure he is working with the correct school account. What would be cool, if possible, would be if when the school id was entered, only that school 's account was enterable smiley_cool .

 

Is that possible?

[touchMe]

Oh yes, that is probably where you are headed not just possible

 

I would have three tables/files, one for students with all your demographic stuff, one for the schools with all their contact data, and one for transactions, each of the files 'talking' to each other through lookups and or related fields

 

What you are discribing would come from the transactions file. A find by schoolID in the transaction file would produce all the payments etc., against that specific school... or you could pull a bunch of reports for groups of schools, schools in different counties etc.,

 

Where are your transactions stored currently?

 

And just for clarity... are you 'taking' cash out of the schools' budgets (available monies) or 'giving' them the cash... are these paper based management transactions or does money actually change hands?