account table & payments table (one to many) create current balanc

[bjh29]

Greetings:

I am looking for help and not sure if I am in the right area, so please
excuse my ignorance if I am out of place.

I have created an account table that calculateds total charges (session fees
+ material fees). I have created a payment table linked to the account table
in a one to many format. The payment table has fields such as material fee
payment & session fee payment, date/time, cash, check etc.

My question is this: How do I create a current balance for each account? I
would like current balance= total charges - sum of payments.

Thank you in advance

 

[Klatuu]

You just need a totals query based on the payments table that is filtered on
the current account table record. The you will need to create calculated
fields based on the formula you provided:
CurrentBalance= [total charges] - [sum of payments]

 

[bjh29]

I haven't done a totals query yet so I hope you can elaborate. I take it I
have to run a query using my account table, but where am I putting the
calculated field that you mentioned? Is this run on my account table or
payment table. And how do I get the two to talk.

Thanks

You just need a totals query based on the payments table that is filtered on
the current account table record. The you will need to create calculated
fields based on the formula you provided:
CurrentBalance= [total charges] - [sum of payments]

Greetings:

I am looking for help and not sure if I am in the right area, so please
excuse my ignorance if I am out of place.

I have created an account table that calculateds total charges (session fees
+ material fees). I have created a payment table linked to the account table
in a one to many format. The payment table has fields such as material fee
payment & session fee payment, date/time, cash, check etc.

My question is this: How do I create a current balance for each account? I
would like current balance= total charges - sum of payments.

Thank you in advance

 

[Klatuu]

It would be run against your payment table.
Your query should have a criteria for the field that identifies the current
account record. That would be which ever field the two tables are joined on.
This will allow you to get the results only for the current account.

There is nothing magic about a Totals query. If you look at the tool bar in
the query builder, you will see an icon that looks like the Greek letter
Omega turned on it's side. The Tool Tip will say Totals if you move the
mouse pointer over it. If you are familiar with Excel, it looks like the icon
you click to create a sum. That is, in fact, what it does.

After you click the Totals icon, you will get a row in the query builder
that lets you choose how you want each column totaled. All columns have to
have a total option.
For the Account ID, it should be "Group By", for the payments, charges, and
the calculated current balance, it should be "Sum"

I haven't done a totals query yet so I hope you can elaborate. I take it I
have to run a query using my account table, but where am I putting the
calculated field that you mentioned? Is this run on my account table or
payment table. And how do I get the two to talk.

Thanks

You just need a totals query based on the payments table that is filtered on
the current account table record. The you will need to create calculated
fields based on the formula you provided:
CurrentBalance= [total charges] - [sum of payments]

Greetings:

I am looking for help and not sure if I am in the right area, so please
excuse my ignorance if I am out of place.

I have created an account table that calculateds total charges (session fees
+ material fees). I have created a payment table linked to the account table
in a one to many format. The payment table has fields such as material fee
payment & session fee payment, date/time, cash, check etc.

My question is this: How do I create a current balance for each account? I
would like current balance= total charges - sum of payments.

Thank you in advance

 

[bjh29]

Thanks! I am getting much closer. Hopefully only one more question. I
currently have my total charges in my account table, not in my payments
table. So when I put a query together using the account Id(pymt tbl - but is
linked to account tbl), total pymts (pymt tbl), total charges (account tbl),
and a current balance (calulated field of total chgs-total pymts, my current
balance shows a negative balance by exactly what the payments total is, the
total charges column is $0. Do I need to rework my tables and place total
charges in the pymts table (possibly with a look up function)? Thanks a ton
for your plain wording responses.

 

[bjh29]

Nevermind! I got it! All I had to do was read your directions more
carefully! Thank you a ton for helping me figure out this last piece to a
very big puzzle. I am very grateful!

 

[Klatuu]

Glad I could help.

There is one little item I would like to make you aware of in database design.
->Never carry calculated values in a table.

I noticed you said you had the current balance in your account table. This
is bad for a number of reasons. First, it is wasting space. Second and most
important, it can get out of sync and present incorrect data. In all cases
where you need to come up with current balance, you should use a version of
that logic. What I normally do is create a function that calculates a value
from my data and use the exact same function every time I need to display
that amount.

 

[bjh29]

Thank you. My calculated values are in a qry. I misspoke, but it is a very
good reminder.