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

Query to calculate liquid used from a tank

P: n/a
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.
Aug 15 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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:
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.
Aug 15 '06 #2

P: n/a
"PeterW" <pw****@btinternet.comwrote in
news:09******************************@bt.com:
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.

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

Aug 15 '06 #3

P: n/a
Many thanks for sharing that usefull procedure, it works a treat.

PeterW
Aug 16 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.