Query to calculate Running Balance, Days Difference and Interest Accrued

[Maria_82]

p.s. Database is attached and pdf file of how query QTrans should look like after your help!

I have 3 Tables – One named TClients, another TAccounts and another TTrans (Transactions).
The TTrans (Transactions) table consists of the following fields: TrID (AutoNumber), AccID (Reference to Account Number in TAccounts Table), Int = Interest Rate (percentage), TrDate (Transaction date) Debit and Credit fields (both set as Number).
I’d like a QTrans to pull data from the TTrans (Transactions) and insert the following 3 calculated fields:



1. ‘Balance’ field for each account = Running sum on ‘Total’, but for each account number and on last date occurrence in case of duplicate dates;
2. ‘Days’ field to calculate the difference in days between each transaction on the same account number- to show adjacent to ‘Balance’ field.
3. ‘Interest’ field to calculate accrued interest – adjacent to where Days field is not null.

Any help is much appreciated

[rpeare]

Don't do those kind of running calculations in a query, they are extremely resource intensive and will make your query run extremely slowly (especially as your datasets get larger). Instead consider performing the calculations on a report. If you are married to the idea of putting this in a query, even though it's a bad ideal, you will have to use domain functions like dsum, dcount etc to make it happen.

[Maria_82]

Don't do those kind of running calculations in a query, they are extremely resource intensive and will make your query run extremely slowly (especially as your datasets get larger). Instead consider performing the calculations on a report. If you are married to the idea of putting this in a query, even though it's a bad ideal, you will have to use domain functions like dsum, dcount etc to make it happen.

Thanks rpeare, Many thanks for your reply & advice. The running sum is relatively easy on a report, however would you know how to perform the other two fields - the Days difference and Interest on a report? I'm tried it all & feel so lost!

[rpeare]

It's not apparent to me where your divisions are.

For instance if I look at ACCID the balance, days and interest do not seem to match up with any division there
Nor do they for ACCNO

If I look at the date field there's no reliable way to determine when/where the calculation is supposed to be performed.

For instance you have a blance/days/interest line on TRID line 2 which seems to be counting transactions for account number 1 on the last date of activity. But there is no such line on ACCID/ACCNO 2. ACCID/ACCNO 3 has several different divisions and I can't see anything in the data itself that helps identify where the divisions occur, it looks like individual dates within a particular ACCNO but it's not consistent, for instance the first range says 3 but there are only 2 dates (both of them 2/11 for account 3) not 3.

[Maria_82]

You're right. I calculated the Balance, Days and Interest fields manually - and didn't notice mistakes! my bad!

I hoped a database would help set a clear picture, but indeed it didn't!

I'll try to explain my requirements better - I need to calculate the end balance & interest between two transaction dates on the same account, for each account.

Thanks again for your time and patience.

[rpeare]

So in your example data (the .pdf document) there should only be 3 subtotals:

Subtotal for customer 1 account 1
Subtotal for customer 1 account 2
Subtotal for customer 2 account 3

Is that correct?
how are you applying the interest is it:

(# days of activity/365) * Interest Rate

or is it:

(# of days in the time span/365) * Interest Rate?

[Maria_82]

Subtotal for customer 1 account 1
Subtotal for customer 1 account 2
Subtotal for customer 2 account 3 for dates 02/11/2014 and 05/11/2014 and interest for each as follows:

(Subtotal * Interest Rate)/365*Days (in the time span)

(Balance * Interest)/365*Days


guess it's your 2nd option = (# of days in the time span/365) * Interest Rate

[rpeare]

Subtotal for customer 1 account 1
Subtotal for customer 1 account 2
Subtotal for customer 2 account 3 for dates 02/11/2014 and 05/11/2014 and interest for each as follows:

(Subtotal * Interest Rate)/365*Days (in the time span)

(Balance * Interest)/365*Days


guess it's your 2nd option = (# of days in the time span/365) * Interest Rate

You're losing me when you modified my third subtotal.

In your previous post you had:

I need to calculate the end balance & interest between two transaction dates on the same account, for each account.

According to this quote the minimum separator for subtotals is your ACCOUNT number. If that's they case why would you have a subtotal for 2/11/2014 AND 5/11/2014.

Can you reproduce an example that actually has correct values because it seems to me you're directly contradicting items in this request.

[Maria_82]

Excuse me for the trouble & misunderstanding...

I need a subtotal per ACCOUNT number, per day.

Hope this helps... i'll work on a proper example shortly.