Home > OS >  Find the highest value within a specified range of values
Find the highest value within a specified range of values

Time:11-28

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

CodePudding user response:

If you add an autonumber ID to your table, you can use my RowNumber function from my project enter image description here

' 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

CodePudding user response:

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
  • Related