Results 1 to 9 of 9
  1. #1
    Maria_82 is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2014
    Posts
    5

    Query to calculate Running Balance, Days Difference and Interest Accrued

    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
    Attached Files Attached Files

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,441
    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.

  3. #3
    Maria_82 is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2014
    Posts
    5
    Quote Originally Posted by rpeare View Post
    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!

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,441
    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.

  5. #5
    Maria_82 is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2014
    Posts
    5
    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.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,441
    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?

  7. #7
    Maria_82 is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2014
    Posts
    5
    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


  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,441
    Quote Originally Posted by Maria_82 View Post
    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.

  9. #9
    Maria_82 is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2014
    Posts
    5
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculate Interest
    By jgelpi16 in forum Queries
    Replies: 3
    Last Post: 08-19-2016, 08:01 PM
  2. Replies: 2
    Last Post: 09-04-2013, 09:48 AM
  3. Replies: 1
    Last Post: 02-12-2013, 03:48 AM
  4. Replies: 5
    Last Post: 04-01-2012, 07:21 AM
  5. Replies: 0
    Last Post: 02-15-2009, 09:14 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums