Results 1 to 10 of 10

Thread: SQL to multiply value from previous record ? How ?

  1. #1
    Board Regular
    Join Date
    Dec 2004
    Posts
    669
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default SQL to multiply value from previous record ? How ?

    Pls help...
    I have a table like this

    ID , Rate
    ==========
    1, 0.2
    2, 0.5
    3, 0.3


    I want the query
    ID , Rate, result
    ==========
    1, 0.2, 0.2
    2, 0.5 , 0.1
    3, 0.3, 0.03

    What is the SQL to achieve this ? Any idea and interim table needed ?
    ie, accumulate multiplication (instead of sum) is needed...

    Thanks
    Last edited by mrchonginhk; Jun 11th, 2019 at 10:45 AM.

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,491
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default Re: SQL to multiply value from previous record ? How ?

    There's no good MSAccess SQL for this that I can think of. I would drop the data into Excel for the calculations here.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,024
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: SQL to multiply value from previous record ? How ?

    In a true relational database, relative order of records in a table has no meaning, which is why this is a foreign concept to Access/SQL, and difficult to do.
    (A good description I once heard is to think of a relational database table as a "bag of marbles", where they are all jumbled up, and no discrenable order to speak of).

    Excel is the better tool for this. But if I had to do this in Access, I would probably create and ADO Recordset in VBA, and order my records by the ID field, and loop through them one-by-one, storing the value from the previous record to apply to the new one. You could then write the result to a new field in the table.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  4. #4
    Board Regular Kyle123's Avatar
    Join Date
    Jan 2012
    Location
    Leeds, UK
    Posts
    2,646
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    2 Thread(s)

    Default Re: SQL to multiply value from previous record ? How ?

    Are you actually using Access? THis is pretty straightforward in other dialects of SQL

  5. #5
    Board Regular
    Join Date
    Dec 2004
    Posts
    669
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL to multiply value from previous record ? How ?

    Quote Originally Posted by Kyle123 View Post
    Are you actually using Access? THis is pretty straightforward in other dialects of SQL
    Thanks. What is the SQL if it is in say MsSQL?

  6. #6
    Board Regular Kyle123's Avatar
    Join Date
    Jan 2012
    Location
    Leeds, UK
    Posts
    2,646
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    2 Thread(s)

    Default Re: SQL to multiply value from previous record ? How ?

    In MSSQL it would be like this:
    Code:
    ;WITH x AS
    (
      SELECT id, rate, RunningTotal = rate  
        FROM test
        WHERE id = 1
      UNION ALL
       SELECT y.id, y.rate, convert(decimal(18,2),x.RunningTotal * y.rate)
       FROM x 
       INNER JOIN test AS y
       ON y.id = x.id + 1
    )
    
    SELECT id, rate, RunningTotal
      FROM x
      ORDER BY id
      OPTION (MAXRECURSION 10000);

    You could probably do it with the new windowing options in later versions too, that would simplify it even further

  7. #7
    Board Regular
    Join Date
    Dec 2004
    Posts
    669
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL to multiply value from previous record ? How ?

    Quote Originally Posted by Kyle123 View Post
    In MSSQL it would be like this:
    Code:
    ;WITH x AS
    (
      SELECT id, rate, RunningTotal = rate  
        FROM test
        WHERE id = 1
      UNION ALL
       SELECT y.id, y.rate, convert(decimal(18,2),x.RunningTotal * y.rate)
       FROM x 
       INNER JOIN test AS y
       ON y.id = x.id + 1
    )
    
    SELECT id, rate, RunningTotal
      FROM x
      ORDER BY id
      OPTION (MAXRECURSION 10000);

    You could probably do it with the new windowing options in later versions too, that would simplify it even further
    Thanks. I will try to study this SQL and see if I can do it also on Access.

  8. #8
    Board Regular
    Join Date
    Jul 2010
    Posts
    459
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL to multiply value from previous record ? How ?

    If you really want to do it in access do what Joe says in post 4 and use vba to enter the data into a new table every time you run it.

    The issue with the SQL above even if you can translate it to Access (don't think you can) would be if you had a deleted record it wouldn't continue. as the join looks for the ID plue one. You would need to include a ranking and use that instead of the ID.

  9. #9
    Board Regular Kyle123's Avatar
    Join Date
    Jan 2012
    Location
    Leeds, UK
    Posts
    2,646
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    2 Thread(s)

    Default Re: SQL to multiply value from previous record ? How ?

    No, you can't use that SQL in Access, it doesn't support Common Table Expressions.

    You're quite right on using the primary key, the work around is to use the ROW_NUMBER function to create a new sequential ID, I didn't bother writing that version as the OP hadn't confirmed if they were actually using MSSQL.

    However, since it's been asked, if you have holes in the incremental field:
    Code:
    ;WITH y AS 
    (
      SELECT id, rate, rn = ROW_NUMBER() OVER (ORDER BY id)
        FROM test
    ), x AS
    (
      SELECT id, rn, rate, RunningTotal = rate  
        FROM y
        WHERE rn = 1
      UNION ALL
       SELECT y.id, y.rn, y.rate, convert(decimal(18,6),x.RunningTotal * y.rate)
       FROM x 
       INNER JOIN y
       ON y.rn= x.rn + 1
    )
    
    SELECT id, rn, rate, RunningTotal
      FROM x
      ORDER BY id
      OPTION (MAXRECURSION 10000);
    Working demo: http://www.sqlfiddle.com/#!18/75f15/1

  10. #10
    Board Regular
    Join Date
    Jul 2010
    Posts
    459
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: SQL to multiply value from previous record ? How ?

    I think the issue is that the OP wants/needs to use access and was hoping to translate or convert logic used by MSSQL into SQL for access. As you note Access doesn't support CTE although some running totals can be achieved using sub queries.

    In terms of using access here is a quick VBA routine that should give you what you need (as always, if using this then test this with a COPY of your data and not your live database)

    Code:
    Sub getresults()
    Dim db As Database
    Dim CT As String: CT = "Mytable" 'Current Table name
    Dim NT As String: NT = "NewTable" 'New table name
    Dim rs1 As Recordset
    Dim rs2 As Recordset
    Dim Presult As Double 'previous result
    
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("Select ID, Rate FROM " & CT & " Order by ID")
    
    If DCount("[Name]", "MSysObjects", "[Name] = '" & NT & "'") = 1 Then
        db.Execute ("DROP TABLE " & NT)
    End If
    
    db.Execute ("CREATE TABLE " & NT & " (ID Long, Rate Double,Result Double)")
    Set rs2 = db.OpenRecordset(NT)
    
    rs1.MoveFirst
        rs2.AddNew
        rs2!ID = rs1!ID
        rs2!Rate = rs1!Rate
        Presult = rs1!Rate
        rs2!Result = Presult
        rs2.Update
        rs1.MoveNext
        
    Do While rs1.EOF = False
            rs2.AddNew
            rs2!ID = rs1!ID
            rs2!Rate = rs1!Rate
            Presult = Presult * rs1!Rate
            rs2!Result = Presult
            rs2.Update
            rs1.MoveNext
    Loop
            
    Set db = Nothing
    Set rs1 = Nothing
    Set rs2 = Nothing
    
    DoCmd.OpenTable (NT)
    
    End Sub
    EDIT: depending on data you may need to add error handling/rounding to the Presult.
    Last edited by stumac; Jun 14th, 2019 at 06:46 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •