using previous row's closing column in current row's opening column in Access 2007

[ariful alam]

Hello,

I have a ms access table named transaction. The fields are:
trans_id,
trans_date,
product,
storage,
received,
issued

Now, i want to create a select query that include all the columns mentioned above, plus two more columns named,
Opening_balance and Ending_balance in the query.

Opening_balance should contain previous row's Ending_balance column value

and

Ending_balance should contain current row's (Opening_balance + received - issued).

I like to do it using Expression Builder, not using VBA code. Is there any suggestion for it.


Thanks

[PGAC]

Hi ariful alam,



I can think of two ways to get what you want but there is no simple solution that I can think of for what you are asking for.

First Way
=========

If trans_id is an interupted numercial sequence then you could create a query from the trans table and add a calculated field:

trans_id_previous: [trans_id]-1.

Save this query. Create a new query which uses the trans table and the above query you can then link [trans_id] to the [trans_id_previous] field from the query. This way, you have the current record from the table linked to its previous record inthe query. You can then add more calculated fields to get what you want.

To be honest, this is not a particularly nice way of doing it but it does avoid VBA. If there are gaps in the [trans_id] field though then you can forget about doing it this way.

Second Way
==========

Add two new fields to the trans table called [Opening_balance] and [ending_balance] and use VBA code to create either a DAO or ADO recordset which then goes through all records in the table and calculates the opening and ending balances. Ensure records are in the correct order though. This is the better solution and is not affected by gaps in the [trans_id] field.

Good luck.

[ariful alam]

@PGAC, thanks for your reply. Yes, there is trans_id gap as every transaction is based on product and storage group.

When i was failed to get the the opening balance and closing balance using query, i add two field in the named o_balance and c_balance in the table Transaction. And query every corresponding product's and storage's last c_balance to use as new transactions o_balance and o_balance + received - issue = new c_balance.

And Jazak'Allah Khair (Thanks) you.