Bring forward previous day balance

[mikejaytlabustro]

Table:

Date Item Quantity
------------------------------------------------
10/20/2013 Item-A 2
10/20/2013 Item-B 4
10/21/2013 Item-A 2
10/21/2013 Item-B -1
=====================================

On 10/22/2013, if Item was selected, I want to bring forward previous day
balance as of 10/21/2013 for Item-A = 4 or Item-B = 3.

Hope i have presented it clearly and your response could help other beginner programmers. Thank you...

 

[CJ_London]

If you just want to know the balance to the end of yesterday then you would use this query

SELECT Item, Sum(Quantity) AS Balance
FROM myTable
WHERE myDate<Date()
GROUP BY item;

 

[mikejaytlabustro]

If you just want to know the balance to the end of yesterday then you would use this query

SELECT Item, Sum(Quantity) AS Balance
FROM myTable
WHERE myDate<Date()
GROUP BY item;

I would like to automatically compute previous day balance of an Item in PrevBal Field in other table.

 

[CJ_London]

SELECT SomeOtherTable.Item, Sum(Quantity) AS Balance
FROM myTable InnerJoin SomeOtherTable ON myTable.item=SomeOtherTable.Item
WHERE myDate<Date()
GROUP BY SomeOtherTable.item;

If you need further help, please provide more detail on what you have and require - table and field names, relationships, sample data and an example of what you want to actually see.

 

[mikejaytlabustro]

If you need further help, please provide more detail on what you have and require - table and field names, relationships, sample data and an example of what you want to actually see.

Attached is the screen shot of my sample data, Thank you..

 

[CJ_London]

Before I can help properly, I need to know a bit more:

First - You won't be able to do this in a table since both the 'today' and 'yesterday' figures are calculated. Also you are using a reserved word - Date - which will cause problems. See the possible solution below that uses the Date function

This is basically a summary of quantities dispensed, whereas in your first post you wanted the balance - which is it? i.e. does the yesterday and today figure ever get reset to zero or reduced when 'refilled' - perhaps using a negative dispense figure?

Also, need to clarify, you want yesterdays figure - what happens if there was no dispensing yesterday - you would have to complete a dispensed figure of 0 for each of these days - is this done?

Along similar lines, another clarification please - can a pump be selected more than once a day?

It looks to me like you are taking your lead from Excel. In Access your table should look like:

ID - autonumber PK
DispenseDate Date
PumpID - text
Dispensed - double

and you would use a query along the following lines to provide the view you are looking for (note you will need to add an additional 'opening balance' in the dispensed column to get your starting position:

SELECT *, (SELECT Sum(Dispensed) FROM TableA as tmp WHERE pumpID=TableA.PumpID AND DispenseDate<TableA.DispenseDate) AS Yesterday, (SELECT Sum(Dispensed) FROM TableA as tmp WHERE pumpID=TableA.PumpID AND DispenseDate<=TableA.DispenseDate) AS Today 
FROM TableA ORDER BY DispenseDate, PumpID

 

[mikejaytlabustro]

Before I can help properly, I need to know a bit more:

First - You won't be able to do this in a table since both the 'today' and 'yesterday' figures are calculated. Also you are using a reserved word - Date - which will cause problems. See the possible solution below that uses the Date function

This is basically a summary of quantities dispensed, whereas in your first post you wanted the balance - which is it? i.e. does the yesterday and today figure ever get reset to zero or reduced when 'refilled' - perhaps using a negative dispense figure?

Also, need to clarify, you want yesterdays figure - what happens if there was no dispensing yesterday - you would have to complete a dispensed figure of 0 for each of these days - is this done?

Along similar lines, another clarification please - can a pump be selected more than once a day?

It looks to me like you are taking your lead from Excel. In Access your table should look like:

ID - autonumber PK
DispenseDate Date
PumpID - text
Dispensed - double

and you would use a query along the following lines to provide the view you are looking for (note you will need to add an additional 'opening balance' in the dispensed column to get your starting position:

SELECT *, (SELECT Sum(Dispensed) FROM TableA as tmp WHERE pumpID=TableA.PumpID AND DispenseDate<TableA.DispenseDate) AS Yesterday, (SELECT Sum(Dispensed) FROM TableA as tmp WHERE pumpID=TableA.PumpID AND DispenseDate<=TableA.DispenseDate) AS Today 
FROM TableA ORDER BY DispenseDate, PumpID

The Pump has a counter, let's say 10/01/2013 (Opening) the counter reading is 1000 then upon closing the same day, the reading is 1500. The volume dispensed for 10/01/2013 is 500. On the next day 10/02/2013, once the Pump was selected (Using Access Form) the Yesterday textbox should automatically calculate previous day pump reading which is 1500. The pump is selected only once a day. If there's nothing dispensed for a particular day there's no entry also.

"Date" is just a caption, TransactionDate is the fieldname.
Is it possible to make a query first to calculate balances of each pump and then use Dlookup to that query?

Kindly take a look at my View attachment Sample DB Access2007.accdb. I did not start properly this project because this was the biggest challenge for me in order to finish it.