SQL to multiply value from previous record ? How ?

[mrchonginhk]

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

 

[xenou]

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]

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]

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

 

[mrchonginhk]

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?

 

[Kyle123]

In MSSQL it would be like this:

;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]

In MSSQL it would be like this:

;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]

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]

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:

;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]

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)

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.