CODEWITHSUNDEEP
sqldatabasems-access
Arka Sarkar
Arka Sarkar

Reputation: 35

Need help writing recursive query

I have a table in access with the following format:-

ID  |  Name  |  Qty
----+--------+------
  1 |  A1    |  10
  2 |  A2    |  20
  3 |  A1    |  30
  4 |  A2    |  40
  5 |  A1    |  50
----+--------+------

I want to run a query which will return the sum of Qty for each row above it where the Name matches. So, the Output will be :-

ID  |  Name  |  Output
----+--------+---------
  1 |  A1    |  0
  2 |  A2    |  0
  3 |  A1    |  10
  4 |  A2    |  20
  5 |  A1    |  40
----+--------+----------

I am not being able to write the query. I think I need some kind of recursive query, but I'm not very well versed in SQL/Databases.

Upvotes: 2

Views: 67

Answers (1)

Martin K.
Martin K.

Reputation: 1060

Access does not support recursion. The following query should do what you want (i called your table NameQty):

SELECT t1.Id,t1.name,sum(t2.Qty)
FROM NameQty t1
    LEFT JOIN NameQty t2 ON t1.name=t2.name AND t1.Id>t2.Id
GROUP BY t1.Id,t1.name
ORDER BY t1.Id

I think you should also use some other column than ID for the definition of "above".

Upvotes: 1

Related Questions