Query to calculate liquid used from a tank

Hello,

I have a table that contains fields for WeekNumber, OilReceived and
OilRemaining.

WeekNum OilRecd OilRem
4 1200
5 1000 2000
6 1800


To calculate the oil used, I need to subtract this weeks oil remaining from
last weeks oil remaining and add this weeks oil received.

Is there a way to reference the previous recordset value in a query to allow
me to achieve this.

I have done this by making a new table of oil usage using VB, but would
prefer to do it through a query.

Any help would be appreciated.

re: Query to calculate liquid used from a tank

I did this once but it's not going to perform well once you get a
couple thousand records.

I did this to track gas mileage (MPG) in my truck. I read the table
into an array, offset the array by 1 and wrote it back to a temp table.
Then I linked by my Date field and had the previous odometer reading,
the current odometer reading, date, gallons and dollars spent.
Subtract previous odometer from current odometer, divide by gallons and
VOILA!

As I said, it becomes inefficient when you have to read and write
several thousand records this way, but it works pretty quickly on
smaller tables.

PeterW wrote:

re: Query to calculate liquid used from a tank

"PeterW" <pwooly@btinternet.comwrote in
news:09ednaurh7wFsH_ZnZ2dnUVZ8tKdnZ2d@bt.com:
Easy. Create a query from design view.Put the table in the query
twice. From table properties, alias the first as ThisWeek, the
other as lastWeek.

Select each field from thisweek and weeknum, oilrem from
lastweek. In the lastweek.weeknum criteria row,
put [thisweek].[weeknum]-1. run the query and verify that things
line up the way you want.

You'll need to put a record for week 0 in the table, with 0 as
values

Put the criteria for thisWeek.weeknum as 0

Create a calculated field and save the completed query.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

re: Query to calculate liquid used from a tank

Many thanks for sharing that usefull procedure, it works a treat.

PeterW