Results 1 to 5 of 5
  1. #1
    manwe_sulimo is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    9

    how to make a field in query that sum all earlier records until this record?

    halo everyone,



    im trying to create calculated field in a query (or a table) that sum the records in other field, if those records have the same ID (field) and has early or the same date/time (field).

    Click image for larger version. 

Name:	balance.PNG 
Views:	16 
Size:	11.8 KB 
ID:	42077
    i made an exmple in excel, the blue column is the calculated field.
    as you can see, every row with "123" as ID sum the earlier "amount" field.

    thx.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I don't think you can in a query.
    but I have user click button, this dumps the data into a 'report' table,
    then it runs some VB that cycles thru the list to sum the column.
    then a report or query can show it.

  3. #3
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    produce a report table to calculate running sums:
    Code:
    Public Sub RunningSumRpt()
    Dim rst
    Dim nSum As Double
    
      'dump the data into the reporting table
    DoCmd.OpenQuery "qaAddData2RptTbl"
      
      'create running sum
    Set rst = CurrentDb.OpenRecordset("select * from tRunningSumRpt")
    With rst
        While Not .EOF
            nSum = nSum + .Fields("Amt").Value & ""
            .edit
              .Fields("Balance").Value = nSum
            .update
            
            .MoveNext
        Wend
    End With
    Set rst = Nothing
    End Sub

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by ranman256 View Post
    I don't think you can in a query.
    Seems like a pretty straight forward (although inefficient) subquery.

    Code:
    select
       a.id
       , (select sum(b.amount) as sumofamount
          from tbl as b
          where b.id = a.id
          and b.date <= a.date
        ) as balance
    from tbl as a

  5. #5
    manwe_sulimo is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Jan 2015
    Posts
    9
    i didnt fully understand the SQL code, but i got the idea of using the same table twice in the same quey, and its working great, thx!

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

Similar Threads

  1. Replies: 5
    Last Post: 02-01-2020, 05:27 AM
  2. Replies: 6
    Last Post: 02-24-2019, 09:03 AM
  3. Replies: 5
    Last Post: 12-07-2016, 09:28 PM
  4. Replies: 12
    Last Post: 05-30-2014, 07:08 AM
  5. Make new records if an field changes
    By ownlikeaboss in forum Access
    Replies: 1
    Last Post: 10-02-2012, 08:27 AM

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