Calculations on query results

  • Thread starter Thread starter tintin
  • Start date Start date
T

tintin

I'm a relative newbie to Access and I'm pulling my hair out trying to
solve this problem.
I have a table containing fields for serial numbers, dates and a
numerical field for a meter reading. I want to filter out records
common to specific serial numbers and then, from the results, subtract
the second last meter reading from the last and calculate the
difference before moving on to the next serial number and doing the
same until all serial numbers are processed. Once finished I would like
to display a summary showing the "usage" from each serial number.
Can this be done and if so, can someone give me some pointers as to
how. I just cannot get Access to relate one returned records meter
field to the previous record.
Any help much appreciated even if its just "no, it can't be done"
Thanks in advance
Tintin (nearly bald)
 
Tintin,

I think this query will do it. You will of course need to change it
to refer to your own field names and table name. Please post back if
you need any more help with this.

SELECT SerialNumber, Min(MeterReading) AS PreviousReading,
Max(MeterReading) AS CurrentReading, Max(ReadingDate) AS
CurrentReadingDate, Max(MeterReading)-Min(MeterReading) AS Usage
FROM YourTable
WHERE ReadingDate In (SELECT Top 2 ReadingDate
FROM YourTable as T2
WHERE T2.SerialNumber=YourTable.SerialNumber
ORDER BY T2.ReadingDate DESC)))
GROUP BY YourTable.SerialNumber
ORDER BY YourTable.SerialNumber

- Steve Schapel, Microsoft Access MVP
 
Thanks for the reply Steve. I can see why I haven't found the solution
myself - I'm even more of a newbie than I thought. I've been using the
wizards with some very basic tweaks to try and get a result and have to
admit that I will have to figure out how to use your answer.
Sorry to all if I'm lowering the tone here...
 
Tintin,

Open a new blank query in design view. Just close the Show Table
window that initially pops up over the query design window. From the
View menu, select SQL view, and then copy/paste from my previous
message. Edit it to reflect your own table and field names...
hopefully you haven't got spaces or other complicating characters in
your names. Watch for wordwrap in my newsgroup post. If you want to
see what this looks like in design view of the query, or see the
results it produces, again use the View menu and select Design or
Datasheet as applicable. Please post back if there is any problem.

- Steve Schapel, Microsoft Access MVP
 
Tintin,

Maybe I should also have said, regarding the SQL I posted, that the
only things you need to change are:
SerialNumber
MeterReading
ReadingDate
YourTable
.... substituting wherever they occur, the names of your fields for the
first 3, and the name of your actual table for YourTable.

- Steve Schapel, Microsoft Access MVP
 
Steve

It worked a treat and I am mightily impressed. Just one small issue
though. I've found that if a reading is entered which is less than the
previous one, the query returns a positive number. A correct negative
would at least flag up the typo when the invoices are run. Any ideas?
 
Tintin,

Hmmm. The query subtracts the Min from the Max without taking into
account the order this data was entered, i.e. there is an assumption
that the data is correct :-) It would be possible to make a query
that subtracts the reading at the earler date from the reading at the
later date, but it would be more complicated. I myself would try to
handle this at the point of data entry. For example you could use
code along these lines on the BeforeUpdate event of your data entry
form...
Dim LastReading As Long
LastReading = DMax("[MeterReading]","YourTable","[SerialNumber]='"
& Me.SerialNumber & "'"
If Me.MeterReading < LastReading Then
MsgBox "The reading is less than the last!", ,"Invalid entry"
Cancel = True
Me.MeterReading.SetFocus
End If

- Steve Schapel, Microsoft Access MVP
 
Good idea Steve - I should have thought of that. I guess its too easy to
let someone else think for you! Thanks once again for all your help.
 
Back
Top