Help with a Running sum query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Ihave a query with the fields date, stock,amount requested amount returned
and balance. Is there any way to get a running sum in the balance field. I
have tried stock-requested+returned in the query but it only give me the
balance for that record only. I need to know how do I and if possible get
the balance from the previous record to the new record in the stock field.
Any help is appreciated.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Use a correlated subquery in the SELECT clause. E.g.:

SELECT [date], stock, amount_requested, amount_returned,
(SELECT Sum(amount_requested)+Sum(amount_returned)
FROM table_name
WHERE [date] <= A.[date]) As Balance
FROM table_name AS A

If you have any criteria for the main query you'll have to include that
in the subquery. E.g.:

SELECT [date], stock, amount_requested, amount_returned,
(SELECT Sum(amount_requested)+Sum(amount_returned)
FROM table_name
WHERE [date] <= A.[date] AND stock = 'widgets') As Balance
FROM table_name AS A
WHERE stock = 'widgets'

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQbY7roechKqOuFEgEQIJ+gCfVEHWkYAib+So6cv8RQG0crpB5nAAn3xA
dTnBzT15ptoMDrhpATqyNrDl
=3qQO
-----END PGP SIGNATURE-----
 
Back
Top