Running balance with multiple entries the same date

[PhilUp]

I have an Access 2010 Report derived from two tables joined by a UnionQuery for a "look like" bank statement with the following fields:

TransactionID (Primary Key)
TransactionDate
Credit
Debit

The report is based on the following SQL:

SELECT Q1.TransactionID, Q1.TransactionDate, Q1.Credit, Q1.Debit, Sum(Q2.Credit-Q2.Debit) AS Balance, Q1.Inv, Q1.Company, Q1.City, Q1.Country, Q1.ItemDescription, Q1.Currency, Q1.Amount, Q1.Currency2, Q1.PayEff, Q1.Branch, Q1.Year, Q1.Month

FROM qryAccountTransactions AS Q1 INNER JOIN qryAccountTransactions AS Q2 ON (Q2.TransactionDate<=Q1.TransactionDate) AND (Q2.TransactionID>=Q1.TransactionID Or Q2.TransactionDate<>Q1.TransactionDate)

GROUP BY Q1.TransactionID, Q1.TransactionDate, Q1.Credit, Q1.Debit, Q1.Inv, Q1.Company, Q1.City, Q1.Country, Q1.ItemDescription, Q1.Currency, Q1.Amount, Q1.Currency2, Q1.PayEff, Q1.Branch, Q1.Year, Q1.Month
ORDER BY Q1.TransactionDate DESC , Q1.TransactionID DESC;

It runs fine, providing there is only one entry with the same date. If several entries the same day, the balance is calculated randomly. The final is ok again for the next date.

As the data are coming from two separate tables, the TransactionID is not in sequence with the date, although they are Unique.

I have tried to manually put them in sequence, but the problem is the same. It is only a matter related to several entries with the same date.

Can anyone help ?
Thanks

 

[arnelgp]

is transactionID autonumber field on both table.
if so don't include them in your query, since it is being grouped.

 

[Rabbie]

One way to resolve this would be to store the date and time of each transaction. You could just display the date but the records would sort in the correct order.

 

[PhilUp]

Thank you both of you. Yes this also what I thought.

How shall I write the SQL then ?

 

[PhilUp]

Actually, with the time it will be difficult, because we enter the transactions at a later date than they really were done. This would mean to enter the time manually and would not be convenient.

If remove the TransactionID, I will still have several transactions on the same day and the ptoblem will be the same.

Any other idea ?

 

[arnelgp]

is it not possible to Join in transactionID, and leave out the transaction date.

 

[CJ_London]

you need to do your query a bit differently using a subquery

SELECT Q1.*, (SELECT SUM(Credit-Debit) FROM qryAccountTransactions WHERE TransactionDate<=Q1.TransactionDate AND TransactionID<=Q1.TransactionID) AS Balance
FROM qryAccountTransactions AS Q1
ORDER BY Q1.TransactionDate DESC , Q1.TransactionID DESC;

 

[PhilUp]

Thanks Arnelgp, No because the transaction ID from Table "Salaries" has numbers in the 500 range. Table from table "Purchase" has ID's in 4000 range.

 

[PhilUp]

Thank you CJ London,

I have tried your SQL but this one also takes the Transaction ID into account. The Balance results are totally screwed-up.