Find the highest value within a specified range of values

Question

I have an MS Access form in which I enter a value representing each record's SECTION. I want to then programmatically calculate a value for its SUBSECTION, based on the value of the other subsections under that section.

For example:

• 1st record: Section = 1, Subsection = 1.00
• 2nd record: Section = 1, Subsection = 1.01
• 3rd record: Section = 2, Subsection = 2.00
• 4th record: Section = 2, Subsection = 2.01
• 5th record: Section = 2, Subsection = 2.02
• 6th record: Section = 3, Subsection = 3.00

So, when I create a new record with section value of 2, for example, I want to:

• look at the subsection values corresponding to section 2 (that is,the subsections where the number before the decimal point is 2),
• determine what the highest subsection value presently is (here, it would be 2.02),
• assign to a variable the next incremental value under section 2, in this example, the next increment would be 2.03.

Any suggestions or recommendations for the best way to approach this? tia

Answer 1

Here's one solution:

Private Sub cmdCreateSubsection_Click()

    If IsNull(Me.txtBoxSection) Then
        MsgBox "Please enter number in the Section Box", vbOKOnly + vbInformation, "Section"
        Me.txtBoxSection.SetFocus
        Exit Sub
    End If
    
    Me.txtBoxSection = CLng(Me.txtBoxSection)
     
    Dim myval As Double
    myval = Nz(DMax("Subsection", "t_subsection_tracker", "Section = " & [Forms]![f_subsection_tracker]![txtBoxSection]), 0)
    
    If myval = 0 Then
        Me.txtBoxSubsection = Me.txtBoxSection
    Else
        Me.txtBoxSubsection = myval + 0.01
    End If
    
    Dim myset As DAO.Recordset
    Set myset = CurrentDb.OpenRecordset("Select * from t_subsection_tracker where ID = -1", dbOpenDynaset)
    myset.AddNew
    myset!Section = Me.txtBoxSection
    myset!Subsection = Me.txtBoxSubsection
    myset.Update
    myset.Close
    Set myset = Nothing

End Sub

Answer 2

Below is my suggestion.

Lets assume the main table is called table_1, and the section table,is called table_session , also the form for adding records is called frmmainform

1. On the form,you have the following controls

A. Combo box 1 with row source been list of sections,i.e 1,2,3,etc, this you can name section ,you can have a sql statement below SELECT DISTINCTROW sessionname FROM table_session;

B. Combo box 2, with name subsectionlook(this is to look for the last record of sub section based on the session name chosen in combo box 1 in step A) with row source been a select statement from table_1 like SELECT SELECT Max(subsection) FROM table_1 where sessionname =Forms![frmmainform]![session]

Now we have two combo boxes on the form,and we can pick a session name(from combo box) and the last record from sub section based on the session field in the current form.

Next you create a textbox field named subsection

The final steps will be in the vba code, do the following

Private Sub session_AfterUpdate()

    subsectionlook.requery

Private Sub subsectionlook_AfterUpdate()

    subsection.text= subsectionlook.value +0.01

Answer 3

If you add an autonumber ID to your table, you can use my RowNumber function from my project VBA.RowNumbers like this:

SELECT 
    Section, 
    Section + RowNumber(CStr([ID]), CStr([Section]))/100 AS SubSection
FROM 
    YourTable
WHERE 
    RowNumber(CStr([ID])) <> RowNumber("", "", True);

Example:

' Builds consecutive row numbers in a select, append, or create query
' with the option of a initial automatic reset.
' Optionally, a grouping key can be passed to reset the row count
' for every group key.
'
' Usage (typical select query having an ID with an index):
'   SELECT RowNumber(CStr([ID])) AS RowID, *
'   FROM SomeTable
'   WHERE (RowNumber(CStr([ID])) <> RowNumber("","",True));
'
' Usage (typical select query having an ID without an index):
'   SELECT RowNumber(CStr([ID])) AS RowID, *
'   FROM SomeTable
'   WHERE (RowNumber("","",True)=0);
'
' Usage (with group key):
'   SELECT RowNumber(CStr([ID]), CStr[GroupID])) AS RowID, *
'   FROM SomeTable
'   WHERE (RowNumber(CStr([ID])) <> RowNumber("","",True));
'
' The Where statement resets the counter when the query is run
' and is needed for browsing a select query.
'
' Usage (typical append query, manual reset):
' 1. Reset counter manually:
'   Call RowNumber(vbNullString, True)
' 2. Run query:
'   INSERT INTO TempTable ( [RowID] )
'   SELECT RowNumber(CStr([ID])) AS RowID, *
'   FROM SomeTable;
'
' Usage (typical append query, automatic reset):
'   INSERT INTO TempTable ( [RowID] )
'   SELECT RowNumber(CStr([ID])) AS RowID, *
'   FROM SomeTable
'   WHERE (RowNumber("","",True)=0);
'
' 2020-05-29. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function RowNumber( _
    ByVal Key As String, _
    Optional ByVal GroupKey As String, _
    Optional ByVal Reset As Boolean) _
    As Long
    
    ' Uncommon character string to assemble GroupKey and Key as a compound key.
    Const KeySeparator      As String = "¤§¤"
    ' Expected error codes to accept.
    Const CannotAddKey      As Long = 457
    Const CannotRemoveKey   As Long = 5
  
    Static Keys             As New Collection
    Static GroupKeys        As New Collection

    Dim Count               As Long
    Dim CompoundKey         As String
    
    On Error GoTo Err_RowNumber
    
    If Reset = True Then
        ' Erase the collection of keys and group key counts.
        Set Keys = Nothing
        Set GroupKeys = Nothing
    Else
        ' Create a compound key to uniquely identify GroupKey and its Key.
        ' Note: If GroupKey is not used, only one element will be added.
        CompoundKey = GroupKey & KeySeparator & Key
        Count = Keys(CompoundKey)
        
        If Count = 0 Then
            ' This record has not been enumerated.
            '
            ' Will either fail if the group key is new, leaving Count as zero,
            ' or retrieve the count of already enumerated records with this group key.
            Count = GroupKeys(GroupKey) + 1
            If Count > 0 Then
                ' The group key has been recorded.
                ' Remove it to allow it to be recreated holding the new count.
                GroupKeys.Remove (GroupKey)
            Else
                ' This record is the first having this group key.
                ' Thus, the count is 1.
                Count = 1
            End If
            ' (Re)create the group key item with the value of the count of keys.
            GroupKeys.Add Count, GroupKey
        End If

        ' Add the key and its enumeration.
        ' This will be:
        '   Using no group key: Relative to the full recordset.
        '   Using a group key:  Relative to the group key.
        ' Will fail if the key already has been created.
        Keys.Add Count, CompoundKey
    End If
    
    ' Return the key value as this is the row counter.
    RowNumber = Count
  
Exit_RowNumber:
    Exit Function
    
Err_RowNumber:
    Select Case Err
        Case CannotAddKey
            ' Key is present, thus cannot be added again.
            Resume Next
        Case CannotRemoveKey
            ' GroupKey is not present, thus cannot be removed.
            Resume Next
        Case Else
            ' Some other error. Ignore.
            Resume Exit_RowNumber
    End Select

End Function