SQL to multiply value from previous record ? How ?

mrchonginhk

Well-known Member
Joined
Dec 3, 2004
Messages
670
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:

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,508
There's no good MSAccess SQL for this that I can think of. I would drop the data into Excel for the calculations here.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,128
Office Version
365
Platform
Windows
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.
 

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,664
Are you actually using Access? THis is pretty straightforward in other dialects of SQL
 

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,664
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
 

mrchonginhk

Well-known Member
Joined
Dec 3, 2004
Messages
670
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.
 

stumac

Active Member
Joined
Jul 16, 2010
Messages
470
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.
 

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,664
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
 

stumac

Active Member
Joined
Jul 16, 2010
Messages
470
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:

Forum statistics

Threads
1,077,650
Messages
5,335,501
Members
399,020
Latest member
julianjjh1

Some videos you may like

This Week's Hot Topics

Top