CODEWITHSUNDEEP
formsms-accessreport
alamnaryab
alamnaryab

Reputation: 1484

MS Access datasheet with expression at each row

I am creating forms and reports in MS Access and need to display records from transactions table with additional field, which dynamically conditional sum previous total.

TRANSACTIONS
DATE         credit    debit
22-01-2015    100       0
23-01-2015    0         50
25-01-2015    0         100
26-01-2015    200       0

want to display above table in reports & forms as below, notice Balance field is not there in database table

TRANSACTIONS
DATE         credit    debit    balance
22-01-2015    100       0        100
23-01-2015    0         50       50
25-01-2015    0         100      -150
26-01-2015    200       0        50

actually it is query fetched from 2 tables and combined using union clause
and having not unique id. because unique_id of 1 table can cause duplication with unique_id of 2nd table

Upvotes: 0

Views: 144

Answers (1)

alamnaryab
alamnaryab

Reputation: 1484

I have solved this using mysql Query as

SELECT 
   tbl.*, 
   (
    (select sum(debit) from q_balance where q_balance.entry_date<= tbl.entry_date)-(select sum(credit) from q_balance where q_balance.entry_date<= tbl.entry_date)
    ) AS balance
         FROM q_balance AS tbl
         ORDER BY tbl.entry_date;

now I have query and can create forms & reports for this query.

Upvotes: 1

Related Questions