Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Dec 2002
    Location
    Allenhurst, New Jersey
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm having a problem in determining the maximum value within a record. In the table, for each record I have a unique device ID, a counter reading for the device for each month of the year. In addition there is the date of the reading. What I need to determine is the maximum counter reading for the year for each device and the date of the reading. Any help would be greatly appreciated. Using Access 2003. A sample table is attached.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='msceppcc' post='769583' date='07-Apr-2009 21:12']I'm having a problem in determining the maximum value within a record. In the table, for each record I have a unique device ID, a counter reading for the device for each month of the year. In addition there is the date of the reading. What I need to determine is the maximum counter reading for the year for each device and the date of the reading. Any help would be greatly appreciated. Using Access 2003. A sample table is attached.[/quote]
    I don't see the attachment...
    You cannot attach an Access database directly - you have to zip it and attach the zip file. And make sure that you click UPLOAD once after clicking Browse... and selecting the file.

  3. #3
    New Lounger
    Join Date
    Dec 2002
    Location
    Allenhurst, New Jersey
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='769588' date='07-Apr-2009 20:36']I don't see the attachment...
    You cannot attach an Access database directly - you have to zip it and attach the zip file. And make sure that you click UPLOAD once after clicking Browse... and selecting the file.[/quote]
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    This is a terribly inefficient table design. Ideally, you should have a table with just 3 fields:

    - DeviceID
    - ReadDate
    - ReadValue

    With the combination of DeviceID and ReadDate as primary key. You could then create a Totals query that groups by DeviceID and returns the maximum of ReadValue.

    With the present setup, you'll have to modify the query for each new year.

    Is there any chance you could use an improved design?

  5. #5
    New Lounger
    Join Date
    Dec 2002
    Location
    Allenhurst, New Jersey
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I agree, but this is something I inherited. This format is used to accomodate a specalized report that is popular with many users. This is a large database that does many other functions and I was looking for a quick for this problem. When time permits this database will have to be re-worked. Thats for your time.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In the attached version, I created a module with two functions:

    [codebox]Function GetMax(DeviceID As String) As Variant
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim i As Integer
    Set dbs = CurrentDb
    strSQL = "SELECT * FROM Sample WHERE DeviceID=" & Chr(34) & DeviceID & Chr(34)
    Set rst = dbs.OpenRecordset(strSQL)
    ' First reading
    GetMax = rst.Fields(2)
    ' Loop through the rest
    For i = 4 To rst.Fields.Count - 1 Step 2
    If rst.Fields(i) > GetMax Then
    GetMax = rst.Fields(i)
    End If
    Next i
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    End Function

    Function GetMaxDate(DeviceID As String) As Date
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim i As Integer
    Dim GetMax As Variant
    Set dbs = CurrentDb
    strSQL = "SELECT * FROM Sample WHERE DeviceID=" & Chr(34) & DeviceID & Chr(34)
    Set rst = dbs.OpenRecordset(strSQL)
    ' First reading
    GetMax = rst.Fields(2)
    GetMaxDate = rst.Fields(1)
    ' Loop through the rest
    For i = 4 To rst.Fields.Count - 1 Step 2
    If rst.Fields(i) > GetMax Then
    GetMax = rst.Fields(i)
    GetMaxDate = rst.Fields(i - 1)
    End If
    Next i
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    End Function
    [/codebox]
    These functions are used in the query qryMax.

    I'd still like to recommend strongly to change the table design.
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In the attached version, I have converted the Sample table to a normalized table tblData. The code I used is included.

    I have created queries to get the maximum value and the corresponding date per ID. These queries don't need VBA code.

    I have also created a query that emulates the original table; it uses two crosstab queries.
    Attached Files Attached Files

Posting Permissions

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