Dynamic Calculations In A Query

[Survane]

Guys,
I have a query in which I track payments but I wanted to know how I could decrease the customer's total when a payment in made either in full or in installments. If I do a calculation then the current payment is subtracted from the balance but not previously made payments. Please help!!

---------------------
survane@hotmail.com

 

[SkipVought]

Survane,

What's the structure of your table(s)?

What gets added/subtracted to/from what?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[Survane]

Skip,

Say this is my table

CID PmtID PmtAmt BalanceOwed
1 20 25 500
1 22 50 500-25-50 = 425
1 43 100 425-100 = 325

See what I mean??

---------------------
survane@hotmail.com

 

[SkipVought]

You have a non-normalized table. No row should depend on another. Balance,totalse etc, are a REPORTING function and NOT a data STORAGE function.

Rather you should eliminate the Balance column and perform a query to calculate the current balance per customer via a query. Furthermore the DEBIT amount could either be in a separate column OR in the PmtAmt column as a negative and in that case the column name might need to be changes and ALSO the PmtID column would need to reference the DEBIT in some way
[tt]
CID AmtTyp Amount
1 999 -500
1 20 25
1 22 50
1 43 100
[/tt]
Now a query like this would give you account balances...
[tt]
Select Sum(Amount), CID
From YourTable
Group By CID
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884