[RESOLVED] SQL query help

hi... i need help in this query...

Code:

---

SELECT Table1.transaction_account, Table1.transaction_id, Table1.transaction_date, Table1.transaction_amount_credit, Table1.transaction_amount_debit, Sum(Table2.transaction_amount_credit)-Sum(Table2.transaction_amount_debit) AS balance, payees.payee_name, categories.category_name, accounts.account_name
FROM transactions AS Table2, accounts INNER JOIN (categories INNER JOIN (payees INNER JOIN transactions AS Table1 ON payees.payee_id=Table1.transaction_payee) ON categories.category_id=Table1.transaction_category) ON accounts.account_id=Table1.transaction_account
WHERE (((Table1.transaction_account)=Table2.transaction_account) And ((Table2.transaction_date)<=Table1.transaction_date))
GROUP BY Table1.transaction_account, Table1.transaction_id, Table1.transaction_date, Table1.transaction_amount_credit, Table1.transaction_amount_debit, payees.payee_name, categories.category_name, accounts.account_name
HAVING (((Table1.transaction_account)=[?account_number]))
ORDER BY Table1.transaction_date, Table1.transaction_id;

---

an example of the result is this:

Code:

---

transaction_account        transaction_id        transaction_date        transaction_amount_credit        transaction_amount_debit        balance                payee_name                category_name        account_name
1                        5                12-06-2006                250,00 €                        0,00 €                                250,00 €        Payee A                        Category A        Account A
1                        2                27-07-2006                0,00 €                                50,00 €                                220,00 €        Payee B                        Category B        Account A
1                        4                27-07-2006                20,00 €                                0,00 €                                220,00 €        Payee A                        Category B        Account A
1                        1                31-07-2006                100,00 €                        0,00 €                                320,00 €        Payee A                        Category A        Account A

---

as you can see the balance is wrong when you have records in the same day...

thx in advance...

here is the example database...
thx in advance

ok...i done other way... created a trigger to execute a SP everytime transactions table is updated... with this SP i updated an sequencial order field ordereb dy date... this way i can have an unique identifier for the query...

this is the SP...in case anyone have the same issue:

Code:

---

CREATE PROCEDURE up_mM_trg_Transactions  AS
declare @intCustId int
declare @intCounter int
declare curTransaction cursor for

SELECT transaction_id FROM transactions ORDER BY transaction_date ASC
OPEN curTransaction

FETCH NEXT FROM curTransaction
INTO @intCustId

SET @intCounter=1

WHILE @@FETCH_STATUS = 0
BEGIN

EXEC up_mM_upd_transaction_order @intCounter, @intCustId
SET @intCounter = @intCounter + 1

FETCH NEXT FROM curTransaction
INTO @intCustId

END

CLOSE curTransaction
DEALLOCATE curTransaction

GO

---