Q quesiton how to design this advanced query

[bkalimili]

I have a parent form with unique record with 3 fields, business id, journal id and journal date.
Each journal entry in the parent form has, several account details records in subform with amount field.
I am trying to say for each journal entry, sum up all the amounts in amount field, if it is positive, display it as debit and if it is negative display it as credit.
If credit and debit don’t balance, query only those records that don’t show balance of credit and debit.
So how should I do that.
How should my SQL statements look like.

Thanks

 

[Jon K]

I have attached an example.

The main form is based off the query "qryJournalIDWithNonBalanceAmount". The sub-form is based off the table "tblAmount".

The Dr/Cr Balance displayed on the main form is calculated in the text box "txtSum" in the sub-form footer.

Hope it helps.


Note
I have used the following subquery in the query to make it updatable so that data can be edited on the forms:-
(SELECT JournalID FROM tblAmount GROUP BY JournalID HAVING Sum(Amount)<>0)

Running a subquery will take time if the tables are large. To speed up performance, you can use a JOIN instead of a subquery, but the query (and hence the main form) will be non-updatable.
.

 

[bkalimili]

Thanks a lot Jon.

thanks, That did it.