Results 1 to 10 of 10
  1. #1
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149

    Running Total Glitch ?

    Baby Steps they say lol.
    I've got a Query displaying transaction History for a specific invoice. I've added a field to the Query to calculate the running total via building an expression
    SubTotal: Dsum("Totals","InvTransQ")


    It works fine except for the very first SubTotal. It doesnt show the amount in Total but the Difference between the 1st and second record. I understand it in the second rcord and so on but th first?

    Does anyone know how t fix this ?
    Click image for larger version. 

Name:	dsum.JPG 
Views:	22 
Size:	26.4 KB 
ID:	34059


  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    What you are doing is a running sum calc, a very common and often answered topic.

    Every record will show that same value because there is no filter criteria in the DSum(). Also, the fieldname is Total, not Totals - assume that was a typo in the post? And I presume you want the balance for each customer?

    SubTotal: DSum("Total", "InvTransQ", "InvoiceDate<=#" & [InvoiceDate] & "# And CustomerName='" & [CustomerName] & "'")

    Or try nested subquery - review http://allenbrowne.com/subquery-01.html#YTD

    Be aware domain aggregate functions and nested queries can perform slowly in large datasets.

    Alternatively, do the aggregate calc in a report as textbox in report has RunningSum property.


    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    Hi June7!

    Total is not a typo this time lol. Its a alias field I created for the InvTotal field. The Query is based on a union query that shows the invoice details and then the payment from a payments table. The new query then has to calculate the running balance of the invoice total and the payments..
    This query doesnt show th customer info as suggested but rther the invoice detauils. ex. I hav 10 invoices in my database and the user clicks on inv 3 hen it will only show the data for inv 3 re charges and payments.

  4. #4
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    This what I mean by per invoice.

    U can see the problem in the subform ...I think you are correct by saying the dsum criteria is wrong....but I have no idea what to make it

    Click image for larger version. 

Name:	query.JPG 
Views:	16 
Size:	72.7 KB 
ID:	34068

  5. #5
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    My Query has the following fields shown in imageClick image for larger version. 

Name:	dsum01.JPG 
Views:	12 
Size:	83.8 KB 
ID:	34069:

    I read a few sites on the Dsum function and If I understand it correctly it consists of three items ( a Field, a source and criteria).
    In my case the Field is called Total, the source is InvoiceTransactionsQ/ The problem is I dont know what to us as criteria so third ingredit is missing.

    I need the query to to give a running total for each invoice. Ex. If the user clicks the invoice number on a datasheet it displayes only that invoice and a subform with the data from this query to show the transactions for that invoice....the invoice itself and every payment made to this invoice.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Don't think need aggregate query.

    InvoiceTransactionsQ is the query you want to show result of DSum(). It is not the source for the DSum(). The source would be the UNION query you mentioned. What field in the UNION holds the invoice and payment dates? If it is Date, advise not to use Date as a name because it is a reserved word (it is an intrinsic function). What field has the InvoiceID? Post the UNION sql.

    I show an example of DSum with criteria in post 2. Here is another:

    SubTotal: DSum("Total", "InvTransQ", "InvoiceID=" & [InvoiceID] & " And TransDate<= #" & [TransDate] & "#")

    Still easier to do in report. Why is it important to have running sum on form? Another approach is to display report on form.





    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    Okay, maybe I didn't say it clearly...that happens o me from time to time. I'm trying to build a union query from TramsactionQ and PmtQ. TransactionQ is a select query that only shows records for a speciic invoice number. PmtQ is a select query that shows records for all payments on a selected invoice number.So when I put them together it shows the transaction and all the payments for the invoice number. In this union query is where I nee the running total to show. but still oly for the selected invoice nr.

  8. #8
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    I used your code in my 2 select queries and it works perfectly, but when I make them a union query it doesnt.
    Is there a specific code I should put in the SQL view of the uniion query to do this calculation so that it sums from the top?

  9. #9
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    Click image for larger version. 

Name:	dsum01.JPG 
Views:	9 
Size:	31.4 KB 
ID:	34086
    This is my result when I make the union query.
    Click image for larger version. 

Name:	sql.JPG 
Views:	9 
Size:	58.2 KB 
ID:	34088
    And the SQL code........

  10. #10
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    Yaaaay!! Got it to work. Saw your answer on someone elses post about more or less the same thing where you told them to use the union query as the source for a new query then do the dsum. Just tried it and it is perfect now.

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

Similar Threads

  1. Help with running total
    By sconnors in forum Database Design
    Replies: 17
    Last Post: 07-18-2017, 10:26 AM
  2. Replies: 6
    Last Post: 09-14-2015, 06:05 PM
  3. Running total
    By edwardcga in forum Reports
    Replies: 1
    Last Post: 11-28-2013, 12:04 PM
  4. Running total
    By lololthis in forum Queries
    Replies: 5
    Last Post: 06-21-2011, 04:14 PM
  5. Running Total in Subform
    By Scorpio11 in forum Forms
    Replies: 19
    Last Post: 07-03-2010, 05:44 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