By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,480 Members | 2,192 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,480 IT Pros & Developers. It's quick & easy.

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

ariful alam
100+
P: 185
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
Jun 19 '12 #1
Share this Question
Share on Google+
2 Replies


P: 7
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.
Jun 23 '12 #2

ariful alam
100+
P: 185
@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.
Jun 26 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.