Forward previous balance of an item

mikejaytlabustro

Access Database 2007 PH
Local time
Tomorrow, 00:08
Joined
Feb 11, 2013
Messages
93
I have a table composed of the following fields:

1. Entry_ID:
2. Item:
3. Previous_Balance:
4. Increase_Or_Decrease:
5. Current_Balance:

Once the selected item had been entered, i want the Current_Balance of previous entry of that item to be forwarded automatically in Previous_Balance field. Is it possible? I have at least 100 types of items. Thank you in advance and I am hoping that this thread might also help other database designers.
 
You will need to do it a query. Here is an example of how I do it for tracking gas mileage.

Code:
SELECT tblFuel.fID, tblCar.cName, tblFuel.fDate, tblFuel.fGallons, tblFuel.fMileage, tblFuel.Cost, [B][COLOR="Red"]DLookUp("[fMileage]","tblFuel","[fID]=" & [fID]-1) AS [Prior][/COLOR][/B], [fMileage]-[Prior] AS Difference, [Difference]/[fGallons] AS MPG, [Cost]/[Difference] AS CPM
FROM tblFuel INNER JOIN tblCar ON tblFuel.cID = tblCar.cID
WHERE (((tblFuel.cID) Like [forms]![frmSearch].[qcar] & "*"));

The key to finding the previous value is the line highlighted.
 
You will need to do it a query. Here is an example of how I do it for tracking gas mileage.

Code:
SELECT tblFuel.fID, tblCar.cName, tblFuel.fDate, tblFuel.fGallons, tblFuel.fMileage, tblFuel.Cost, [B][COLOR="Red"]DLookUp("[fMileage]","tblFuel","[fID]=" & [fID]-1) AS [Prior][/COLOR][/B], [fMileage]-[Prior] AS Difference, [Difference]/[fGallons] AS MPG, [Cost]/[Difference] AS CPM
FROM tblFuel INNER JOIN tblCar ON tblFuel.cID = tblCar.cID
WHERE (((tblFuel.cID) Like [forms]![frmSearch].[qcar] & "*"));

The key to finding the previous value is the line highlighted.

Thank you.. But if i am going to enter a new record for example Item-B with an ID of 4, and previous entry for Item-A has an ID of 3, i will get the previous balance of Item-A instead of Item-B. I think the code will work only for a table using only a single item?
 

Users who are viewing this thread

Back
Top Bottom