Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2007
    Posts
    6

    Unanswered: Calculate Meter Readings using Function

    I have a number of Copy machines each with its own serial number. I read the meter every month, and I need function to calculate the number of copies made each month, in a query. Has anyone any ideas??

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no I dont have a off the shelf function

    however if you take your monthly meter readings and store them in a sub table referencing your equipment table. then you could access your meter readings over time.

    if you also had a table defining rates you could then generate your invoice by linking the piece of equipment to the amount used, and pull in the relevant rates per equipment.

    the number of pages per cycle can be calculated in several ways
    one could be to SELCET the TOP 2 records from your readings table (ordered by machine_id ASCending and date_meter_read DESCending)
    subtracting the second form the first gives the number of pages used in any one month.

    you could do the same thing in a function.... in practise you probably need to implement it as a VB function. you need to build in some additional loogic to ensure that your two readings cover the relevant period (ie you want to exclude any readings outside the current period). your customer would not be impressed to get the same bill as last period becuase you dont have this periods reading entered.. you should be able to do that with a where clause (or having if you choose to use a group by clause)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2007
    Posts
    6
    I forgot to mention I'm using Access 2002. Can I use SELCET?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Build a new query then look for the "SQL" button - you can type whatever you want in there!
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2007
    Posts
    6
    I can solve the problem if only 1 serial No is used, but there are about 600 Serial No's. I have tried using an alias for the tableName, and Comparing the meter reading with the highest but less than the one with the "Alias Name" That works but I have a problem when I create a Report from the query.
    Are you fooling with me -- with SELCET ? I tried SELECT TOP2, Its works fine with 1 Serial No only, but not with more than one. Any other ideas will be welcome. Thanks again for the help.

  6. #6
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    What sort of copier is it does it have a web inface

    I have a msaccess that reads all 9 copies totals and put then in database.
    on a daily base


    here some code that someone wrote and has been pass on and on and on

    it get the next record

    I use it by nextRecval("cname",[idate],"counter","sorted","idate",[cname])

    cname = copier name
    [idate] = date of the reading value
    "counter" = acturl reading
    "sorted" = is the table/query name
    "Idate" = date feild to sort
    [cname] = copier feild to sort



    Code:
    Function NextRecVal(KeyName As String, KeyValue, FieldNameToGet As String, TableName As String, sort2 As String, also As String)
       '*************************************************************
       ' FUNCTION: NextRecVal()
       ' PURPOSE: Retrieve a value from a field in the next form
       '          record.
       '**************************************************************
       ' add sort2 this
       Dim Db As Database
       Dim rs As Recordset
       Dim SQL As String
       
       Set Db = CurrentDb()
       
       SQL = ""
       SQL = SQL & "SELECT " & TableName & ".*"
       SQL = SQL & " FROM  " & TableName
       SQL = SQL & " Where " & TableName & "." & KeyName & "= " & also & ""
       SQL = SQL & " ORDER BY " & TableName & "." & KeyName & "," & TableName & "." & sort2
       
       'Debug.Print SQL
       On Error GoTo Err_NextRecVal
       
       ' The default value is zero.
       NextRecVal = 0
    
       ' Get the form recordset.
       Set rs = Db.OpenRecordset(SQL)
       ' Find the current record.
       Select Case rs.Fields(sort2).Type
          ' Find using numeric data type key value?
          Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
                DB_DOUBLE, DB_BYTE
             rs.FindFirst "[" & sort2 & "] = " & KeyValue
          ' Find using date data type key value?
          Case DB_DATE
             rs.FindFirst "[" & sort2 & "] = #" & Format(KeyValue, "mm/dd/yyyy hh:mm:ss") & "#"
          ' Find using text data type key value?
          Case DB_TEXT
             rs.FindFirst "[" & sort2 & "] = '" & KeyValue & "'"
          Case Else
             MsgBox "ERROR: Invalid key field data type!"
             Exit Function
       End Select
    
       tempit = rs(KeyName)
       
       
       ' Move to the next record.
       rs.MoveNext
       
       tempit2 = rs(KeyName)
       
       
       If tempit = tempit2 Then
          ' Return the result.
       NextRecVal = rs(FieldNameToGet)
       Else
       NextRecVal = 0
       End If
       rs.Close
    Bye_NextRecVal:
        Exit Function
    Err_NextRecVal:
     NextRecVal = 0
        Resume Bye_NextRecVal
    End Function
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    As another option, here's an SQL method that calculates the difference between current and previous meter readings, from a sample I fixed for someone else:

    SELECT Power.MeterNo, Power.PDate, Power.PMeterReading, (SELECT Prev.PMeterReading FROM Power AS Prev WHERE Prev.PDate = (SELECT MAX(Prev2.PDate) FROM Power AS Prev2 WHERE Prev2.PDate < Power.PDate AND Prev2.MeterNo = Power.MeterNo) AND Prev.MeterNo = Power.MeterNo) AS Prev, [PmeterReading]-[Prev] AS Diff
    FROM Power
    ORDER BY Power.MeterNo, Power.PDate DESC;
    Paul

  8. #8
    Join Date
    Feb 2007
    Posts
    6
    Thanks Paul, that worked first time. Look me up when you are in Cape Town Next. I owe you.

  9. #9
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Since you owe me, you better come here. That's like a 30 hour flight. I'll never get my wife on the plane.
    Paul

  10. #10
    Join Date
    Feb 2007
    Posts
    6
    Just when you think you're on top! I have created a report from the query, which works fine, except, when I need to total the columns. I get a "Multi-level Group By clause is not allowed in a subquery" Are there any tips for getting around this?

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Sounds like you need to add your calculations to the SQL.

    In your SELECT statement add your calculation "AS 'Calc' "
    Then add 'Calc' to your Group by clause.

    feel free to ask some more - perhaps post what you're trying to calculate that fails
    George
    Home | Blog

  12. #12
    Join Date
    Feb 2007
    Posts
    6
    The report from the query looks something like this:
    Approx 210 copiers
    Column Headings are: SerialNo, DateOfReading, CopiesMade, Cost; and I need to add the CopiesMade and Cost. My SQL in the query looks like this:
    PrevMeter1: NZ((SELECT PrevMeter1.Meter1 FROM MeterReadingsTable AS PrevMeter1 WHERE PrevMeter1.DateOfReading = (SELECT MAX(PrevMeter12.DateOfReading) FROM MeterReadingsTable AS PrevMeter12 WHERE PrevMeter12.DateOfReading < MeterReadingsTable.DateOfReading AND PrevMeter12.SerialNo = MeterReadingsTable.SerialNo) AND PrevMeter1.SerialNo = MeterReadingsTable.SerialNo),0) and the subtract this from the current Reading. Is this more to work on?

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT PrevMeter1.Meter1, <insert calculation> AS 'Calc'
    ...
    GROUP BY PrevMeter1.Meter1, Calc
    Give that a try
    George
    Home | Blog

  14. #14
    Join Date
    May 2016
    Posts
    1

    Help a simpleton!!

    Hi, I am new to Access and am just trying to create a very simple database as follows. I have a list of properties that use water from their community water deposit. Each property has its own water meter. The meters are read on a daily basis. I need to create a database that will record the meter readings and calculate the individual consumption each week. I've created the table which has a field which lists the property by number, and another field for the Readings; obviously I don't want to have to create a field for the readings for every week of the year! How can I do this - can anyone help a novice user please?

  15. #15
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Each reading should be a separate record, and include fields such as the property and the date of the reading.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •