Calculate difference between records

[MelodeeLRS]

I have looked all over for a way to calculate the difference between water meeter readings. My brain may be Friday slow, but all I can find is for calculating differences in dates.

My water table has 3 fields: ReadDate, Reading, Calc1

ReadDate is the date the water reading occurred
Reading is the meter reading
Calc1 is calculated (when I find out how!) by subtracting the current reading from the previous day's reading.

I would like to add the write script in my form so when the readings are entered, the calculation auto populates the field for the user.

 

[pbaldy]

How about:

http://allenbrowne.com/subquery-01.html#AnotherRecord

 

[MelodeeLRS]

I have two issues. The first is thst I have limited knowledge of SQL and VBA. The other is that I don't want to have to create a query for each well (there are almost 50)...I need a way to insert the code in the form where the date and reading is entered by the user. This will allow the user to not have to calculate it by hand.

 

[plog]

First, Calc1 shouldn't be a field in a table. You don't store calculated values--you calculate them. So if you want it to appear on a form, you would use an unbound control and then populate it by building code that will calculate the data.

You will need to use a DMax(http://www.techonthenet.com/access/functions/domain/dmax.php) inside a Dlookup(http://www.techonthenet.com/access/functions/domain/dlookup.php) to get the last Reading.

My suggestion is to put an unbound box on your form, then make it populate with the value of the date in your table that is just before the date appearing in the ReadDate input on your form. This will involve just the Dmax function. See if you can get it working then post back here with what you have done and any issues that you have.

 

[jdraw]

There must be more to your database than you have shown. ReadDate and Reading?
What about Customer, Address, MeterId.....

If you are using a database system (Access) for this calculation, you might want to go to excel. If you do have more info about your proposed database, then please tell the readers. They're willing to help once they understand your issues.

 

[MelodeeLRS]

Thank you for your responses! They did help point me in a direction for resolution.

Since I don't understand the Vulcan in nesting the referenced statements (an idea of what each actully does would be nice!) and I am short on time (reports have to be ready for monday morning), I exported all the tables to Excel, then linked them back to the Access file. In each Excel table I used an IF statement to calculate the difference in the readings for each day. Then I changed the field in the Access forms to be read only. The user still only has to enter each day's reading and the gallons used are autocalculated and available for use on the weekly and monthly reports.

I am still not comfortable keeping the data stored in Excel...especially as the data has to be kept for eternity...but I have something that works, which is the goal today.

Thanks again for your help!

 

[jdraw]

MelodeeLRS,

Glad you have a solution to get you past your deadline.
However, my intent was to get you to tell us the whole story -- the 30000 foot version. What is it you are trying to do? What are the things involved? Do you have some business rules? The key to database is a sound table design.
But a Meter reading application that involves only Reading and ReadingDate is not a complete application. What's the full picture.

A good tutorial for learning database design is this but you'll have to work through it. It does have answers included.

Also, from posts and experience, it seems knowing Excel/spreadsheet may be harmful when learning database. Reality is you may have to unlearn some of the concepts/truths you know dearly.

Good luck.