查找指定值范圍內的最高值

我有一個 MS Access 表單，我在其中輸入了一個表示每條記錄的 SECTION 的值。然后，我想根據該部分下其他小節的值，以編程方式計算其 SUBSECTION 的值。

例如：

• 第一條記錄：部分 = 1，子部分 = 1.00
• 第二條記錄：部分 = 1，子部分 = 1.01
• 第三條記錄：部分 = 2，子部分 = 2.00
• 第 4 條記錄：Section = 2，Subsection = 2.01
• 第 5 條記錄：Section = 2，Subsection = 2.02
• 第 6 條記錄：Section = 3，Subsection = 3.00

因此，例如，當我創建一個部分值為 2 的新記錄時，我想：

• 查看section 2對應的subsection值（即小數點前數字為2的subsection），
• 確定目前最高的分段值是多少（這里是 2.02），
• 將第 2 部分下的下一個增量值分配給變數，在此示例中，下一個增量為 2.03。

有關解決此問題的最佳方法的任何建議或建議？蒂亞

uj5u.com熱心網友回復：

如果向表中添加自動編號 ID，則可以使用RowNumber我的專案

' 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

uj5u.com熱心網友回復：

下面是我的建議。

假設主表稱為 table_1，節表稱為 table_session ，添加記錄的表單稱為 frmmainform

1. 在表單上，??您有以下控制元件

A. 組合框 1 行源是節串列，即 1,2,3 等，你可以命名節，你可以在下面有一個 sql 陳述句 SELECT DISTINCTROW sessionname FROM table_session;

B. 組合框 2，名稱為 subsectionlook（這是根據在步驟 A 中組合框 1 中選擇的會話名稱查找子節的最后一條記錄），行源是來自 table_1 的選擇陳述句，如 SELECT SELECT Max(subsection ) FROM table_1 where sessionname =Forms![frmmainform]![session]

現在表單上有兩個組合框，我們可以根據當前表單中的會話欄位選擇會話名稱（來自組合框）和子部分的最后一條記錄。

接下來創建一個名為 subsection 的文本框欄位

最后的步驟將在 vba 代碼中，執行以下操作

私人子會話_AfterUpdate()

    subsectionlook.requery

私有子小節look_AfterUpdate()

    subsection.text= subsectionlook.value  0.01

標籤：sql vba ms-access

上一篇：在formattable中設定條形大小

下一篇：MSAccess-文本框與組合框