running total issue

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

Guest

Hello there,

I am using the follwing SQL code to have my running total:

SELECT A.Date, A.ID, A.Amounts, A.Type, A.[Account Desc], A.Description,
A.Balance, A.Currency, A.Banks, [balance]+(SELECT Sum(amounts)
FROM Bankbalancequery
WHERE [date] <= A.[date]) AS Balances
FROM Bankbalancequery AS A
ORDER BY A.Date, A.ID;

The problem is that I have more than one transaction for the same date, so I
am getting the same balance for the different records having the same date.

Is there a way out plzzzzzzzz?

thks.
 
You need some way to distinguish the different transactions on the same
date.

Presumably ID is the primary key and the secondary sort is on this field, so
looks like the best candidate.

SELECT A.Date, A.ID,
A.Amounts,
A.Type,
A.[Account Desc],
A.Description,
A.Balance,
A.Currency,
A.Banks,
[balance]+(SELECT Sum(amounts)
FROM Bankbalancequery
WHERE (Bankbalancequery.[date] <= A.[date])
AND (Bankbalancequery.ID < A.ID)) AS Balances
FROM Bankbalancequery AS A
ORDER BY A.Date, A.ID;

Hopefully you don't really have a field named Date.
 
Allen,

Thank you very much for your help, it worked perfectly, this is what i was
looking for.

Thank you again.

Cheers.

Allen Browne said:
You need some way to distinguish the different transactions on the same
date.

Presumably ID is the primary key and the secondary sort is on this field, so
looks like the best candidate.

SELECT A.Date, A.ID,
A.Amounts,
A.Type,
A.[Account Desc],
A.Description,
A.Balance,
A.Currency,
A.Banks,
[balance]+(SELECT Sum(amounts)
FROM Bankbalancequery
WHERE (Bankbalancequery.[date] <= A.[date])
AND (Bankbalancequery.ID < A.ID)) AS Balances
FROM Bankbalancequery AS A
ORDER BY A.Date, A.ID;

Hopefully you don't really have a field named Date.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sam said:
Hello there,

I am using the follwing SQL code to have my running total:

SELECT A.Date, A.ID, A.Amounts, A.Type, A.[Account Desc], A.Description,
A.Balance, A.Currency, A.Banks, [balance]+(SELECT Sum(amounts)
FROM Bankbalancequery
WHERE [date] <= A.[date]) AS Balances
FROM Bankbalancequery AS A
ORDER BY A.Date, A.ID;

The problem is that I have more than one transaction for the same date, so
I
am getting the same balance for the different records having the same
date.

Is there a way out plzzzzzzzz?

thks.
Click to expand...
Click to expand...
 
Back
Top