Running Sum, Days Difference and Accrued Interest

[Maria_82]

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.

p.s. Attached is the database I'm using together with a pdf snapshot of the results I would require.

Any help is much appreciated

 

[plog]

I don't think TTrans is properly structured. You shouldn't have a field for Credits and a field for Debits, you should just have an amount field. Nor do you need both the AccID and TrAccNo fields. That relationship is defined in TAccounts so having both those fields in TTrans is redundant.

Now for the query you want to produce, I'm having trouble seeing the logic for which records gets the calculated fields. How come there are 4 records for AccID=3, TrDate=8/11/2014 and none of them have Days calculated?

For that matter how come only 1 of the 2 records for AccID=2, TrDate=5/11/2014 have Days calculated? What logic determines which ones gets Days calculated?

And to save me time, whats the formula for the Interest field?

 

[Maria_82]

1. You're right, I can replace the Debit & Credit fields with Amount. AccID can also be left out from TTrans. TrAccNo in TTrans, however is required to associate each transaction to an account number.

2. With regards to the transactions dated 08/11/2014, Days are not calculated as there aren't any transactions on AccID=3 after 08/11/2014. So the days field will be updated once a new transaction after 08/11/2014 will be keyed - ie to be able to calculate the date difference between the new date and 08/11/2014.

3. In my example, the days are only calculated on the on the 'last' similar date field. ie if there are two similar dates on 05/11/2014, days are only calculated on the last occurrence. This is not obligatory, days can be calculated all through the list, between the previous and last dates, however Interest must be only calculated on the last day of each occurrence.

4. Formula for Interest field:= (Balance*Interest)/365*Days

Thanks again for your swift reply - You already helped me much!

 

[plog]

1.

AccID can also be left out from TTrans. TrAccNo in TTrans, however is required to associate each transaction to an account number.

Practically you are correct, but technically you are wrong. What's the point of AccID then? You made it the primary key in TAccounts, therefore AccID should be the foreign key in any other table. I don't think you need AccID at all (even in TAccounts) if TrAccNo can be used as the Primary key in TAccounts. However, you designated AccID as the primary key, so it should be used as the foreign key when you need to link a table to TAccounts.

2.

Days are not calculated as there aren't any transactions on AccID=3 after 08/11/2014

That logic doesn't make sense in the context of your other data. Look at AccID=1 & TrDate=3/1/2014--there are no transactions after it for AccID=1 but it still has a Days value. How come it has a value but the one I initially referenced doesn't?

3. 'Last' is a very ambigous word, explain how you logically determine 'last' on your data? Do you have a time value I am not seeing? Explain how TrID=4 is last when compared to TrID=5?

 

[Maria_82]

You're correct on all three points above. 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.

 

[plog]

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

Much better. When you bring in additioanl fields, especially when aggregating data its so easy to make a mess of things. Bring in just the data you need.

With that said, you will need a sub-query to accomplish what you now want. Also, I am going to assume you changed the Debit/Credit fields in TTrans to Amount. This SQL will be that sub-query:

SELECT TTrans.AccID, TTrans.TrDate, DSum("[Amount]","TTrans","[AccID]=" & [AccID] & " AND [TrDate]<=#" & [TrDate] & "#") AS Balance, DateDiff("d",DMax("[TrDate]","TTrans","[AccID]=" & [AccID] & " AND [TrDate]<#" & [TrDate] & "#"),[TrDate]) AS Days
FROM TTrans
GROUP BY TTrans.AccID, TTrans.TrDate, DSum("[Amount]","TTrans","[AccID]=" & [AccID] & " AND [TrDate]<=#" & [TrDate] & "#"), DateDiff("d",DMax("[TrDate]","TTrans","[AccID]=" & [AccID] & " AND [TrDate]<#" & [TrDate] & "#"),[TrDate]);

Paste the above SQL into a query, save it naming it something like 'sub_DailyTotals' and run it. It should give you 90% of what you need.


The other 10% will come in the main query. You will link that sub-query to the TAccounts table, bring down all relevant fields and then do a calculation to determine the interest.

Let me know if you have any issues or if I am way off.

 

[Maria_82]

Thanks for reply & code. I have changed Debit & Credit to Amount as advised and created a sub directory with the sql you proposed. I'm not sure whether I did everything correctly, but the results aren't quite there.

I have attached an updated file for your perusal.

Thanks again....

p.s. If as yo mentioned earlier you think a Report is easier & faster, please don't waste your time on queries - a report will still work fine for me.

 

[plog]

Why is QTrans using TTrans? I though you needed a daily balance query?

 

[Maria_82]

I need a daily balance query on each account, but it only worked well for AccID 1 & 2 on sub-DailyTotals. For some reason, AccID 3 has no daily balance results on sub-DailyTotals.

I used QTrans as the main query you advised above.

 

[plog]

I need a daily balance query on each account

That's not what you said in post #5.

AccID 3 has no daily balance results on sub-DailyTotals.

Yes it does, I just ran the database you posted and it has a value in the Balance for every day it has transactions.

I'm at a loss at this point, I don't know what you think you want, nor what you actually want. I don't think you do either.

 

[Maria_82]

I attached a snapshot of what my subquery is showing. Is it the same as yours?

 

[plog]

No. I opened your database and it is giving me different data. My screenshot is attached.

 

[Maria_82]

Hi plog! Thanks for your snapshot - unlike mine, yours sure looks right. I'm not sure what's causing our queries to differ-maybe some setting in access? Could you send me the database to see whether I get the same results, please?

Thanks for your time & help- appreciate!

 

[plog]

Attached is the one I got the screenshot from.

 

[Maria_82]

Thanks ... unfortunately same results here!

I'll try it on a different pc tomorrow!

Thanks again!

 

[plog]

Change the Balance field to this calculation and see what you get:

Balance: DSum("[Amount]","TTrans","[AccID]=" & [AccID])

Does the subquery show values for every record now?

 

[Maria_82]

Change the Balance field to this calculation and see what you get:

Balance: DSum("[Amount]","TTrans","[AccID]=" & [AccID])

Does the subquery show values for every record now?

Hi thanks, I tried file on another pc at work & still same results, unfortunately.

I also downloaded the database you sent me on your previous post and updated the subquery Balance field and obtained the attached (screenshot2) balances, which although show on every record, they don't seem right.

Thanks the same!

 

[plog]

The values, aren't right, but they are there and that's what we were hoping for. Now we know the date criteria is what was causing the snafu. We started with this:

Balance: DSum("[Amount]","TTrans","[AccID]=" & [AccID] & " AND [TrDate]<=#" & [TrDate] & "#")

And it didn't show data until we took off the [TrDate] parts. So that means its the culprit.

My suggestion is to play around with the date criteria portion (the [TrDate] parts). It might be they don't need the number sign (#), maybe they should be inside single quotes, it may have to do with the format you are using so you might put them inside a CDate (http://www.techonthenet.com/access/functions/datatype/cdate.php).

Without having a system that is causing the error I can't help you debug this, but I can confirm its that date part of the expression doing it.