Calculating in a Query

[RuthD]

Hello All,
I am building a database and need to add and subtract payments received from parents. To start with the parents of children attending the daycare will have a balance of $150. Once a payment has been posted, I would like it to subtract the payment and give the account balance. I need the balance to roll over into the next row even if the balance is zero I need that to show. I created a query for this but that is as far as I can get. Any help will be greatly appreciated.
Thank you!
Ruth

 

[plog]

First, you need to clean up a few things and change your thinking on others.

1. You have an autonumber primary key in Families (FamilyNumber), but you don't use it. That number should be in every record of the Children table and should be used in the Payments table instead of FamilyName. There's more than one 'Smith' family in the world.

2. You shouldn't store calculated values. That means the 'AccountBalance' field in Payments and Families should be eliminated.

3. I would rename the payments table to 'Transactions' and think of it like that. Instead of just payments, you would also store debits--i.e. their initial balance. This will help with building a query to get their balance.

4. You need to create a query to replace the 'AccountBalance' fields. This will be an aggregate query and here's some help with that: http://www.599cd.com/tips/access/aggregate-query/

 

[RuthD]

I have made some of the changes you recommended. I left the information in the Children's table the same because I want to connect that table to the Family table by the Primary Parents name. With those changes made I am having problems getting the query to give an automatic calculated total.

 

[plog]

What's your problem specifically? Can you post your current database?

 

[RuthD]

I am trying to show payments and balances automatically in the Family and Transactions tables. In the Children's table I want to pull up the family information by primary parent due to the center contacting the parent by the child/ren's name/s.
(I don't see the icon to attach the db so I'll do that in a seperate post.)

 

[RuthD]

I found it.

 

[plog]

I am trying to show payments and balances automatically in the Family and Transactions tables.

Balances aren't going to be in these tables, its going to be in a query based on the Transactions table. This means you need to eliminate 'OutstandingBalance' field from your Transactions table and you have to explain to me what the fields 'ThisPayment' and 'AmountPaid' represent and how those are different.

From what I see, you need 4 fields in your Transactions table:

FamilyNumber, TransactionType, Amount, TransactionDate

Transaction Type will be a link (like FamilyNumber) to another table (i.e. TransactionTypes) that contain various types (i.e. Payment, Fee Charge) and if that Transaction type is a credit or debit. I would set TransactionTypes up like this:

TransType, TransValue
Payment, -1
Fee Charge, 1

-1 would be for credits to the count, 1 would be for debits.