Forward previous balance of an item

[mikejaytlabustro]

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.

 

[Alansidman]

You will need to do it a query. Here is an example of how I do it for tracking gas mileage.

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.

 

[mikejaytlabustro]

You will need to do it a query. Here is an example of how I do it for tracking gas mileage.

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?

 

[Alansidman]

I think the code will work only for a table using only a single item?

You are correct. I missed the part about multiple items.

 

[Alansidman]

In another forum, I found something that may be helpful. Look at this link

Look at the thread 13.

Alan

http://www.accessforums.net/queries/vehicle-fuel-consuption-37252.html