如何通过MDB查询更新BegAtt & EndAtt的值？

Question

我想根据MDB by query中的Control Start、Control Stop、Application和Record Type字段更新BegAtt和EndAtt的值。下面是我的MDB数据库的记录集，其中我要基于记录类型为Email的父记录更新BegAtt和EndAtt值：

Application             Record Type         Control Start       Control Stop        BegAtt  EndAtt
Outlook Mail Document   Email               3rd-Party_00000040  3rd-Party_00000040      
Adobe Acrobat Document  Email-Attachment    3rd-Party_00000041  3rd-Party_00000044      
Adobe Acrobat Document  Email-Attachment    3rd-Party_00000045  3rd-Party_00000045      
Adobe Acrobat Document  Email-Attachment    3rd-Party_00000046  3rd-Party_00000049      
Adobe Acrobat Document  Email-Attachment    3rd-Party_00000050  3rd-Party_00000181      
Adobe Acrobat Document  Email-Attachment    3rd-Party_00000182  3rd-Party_00000223      
Adobe Acrobat Document  Email-Attachment    3rd-Party_00000224  3rd-Party_00000243      
Adobe Acrobat Document  Email-Attachment    3rd-Party_00000244  3rd-Party_00000250      
Adobe Acrobat Document  Email-Attachment    3rd-Party_00000251  3rd-Party_00000460      
Outlook Mail Document   Email               3rd-Party_00000461  3rd-Party_00000461      
Adobe Acrobat Document  Email-Attachment    3rd-Party_00000462  3rd-Party_00000611      

BegAtt值应该是主记录的第一个值(记录类型=电子邮件)，EndAtt值应该是家族的最后一个值(记录类型=电子邮件附件)。以下是期望的结果：

Application             Record Type         Control Start       Control Stop        BegAtt              EndAtt
Outlook Mail Document   Email               3rd-Party_00000040  3rd-Party_00000040  3rd-Party_00000040  3rd-Party_00000460
Adobe Acrobat Document  Email-Attachment    3rd-Party_00000041  3rd-Party_00000044  3rd-Party_00000040  3rd-Party_00000460
Adobe Acrobat Document  Email-Attachment    3rd-Party_00000045  3rd-Party_00000045  3rd-Party_00000040  3rd-Party_00000460
Adobe Acrobat Document  Email-Attachment    3rd-Party_00000046  3rd-Party_00000049  3rd-Party_00000040  3rd-Party_00000460
Adobe Acrobat Document  Email-Attachment    3rd-Party_00000050  3rd-Party_00000181  3rd-Party_00000040  3rd-Party_00000460
Adobe Acrobat Document  Email-Attachment    3rd-Party_00000182  3rd-Party_00000223  3rd-Party_00000040  3rd-Party_00000460
Adobe Acrobat Document  Email-Attachment    3rd-Party_00000224  3rd-Party_00000243  3rd-Party_00000040  3rd-Party_00000460
Adobe Acrobat Document  Email-Attachment    3rd-Party_00000244  3rd-Party_00000250  3rd-Party_00000040  3rd-Party_00000460
Adobe Acrobat Document  Email-Attachment    3rd-Party_00000251  3rd-Party_00000460  3rd-Party_00000040  3rd-Party_00000460
Outlook Mail Document   Email               3rd-Party_00000461  3rd-Party_00000461  3rd-Party_00000461  3rd-Party_00000611
Adobe Acrobat Document  Email-Attachment    3rd-Party_00000462  3rd-Party_00000611  3rd-Party_00000461  3rd-Party_00000611

我尝试了下面的代码，但得到了错误的结果。

SELECT [3rd-Party001_Main].[Record Type], Min([3rd-Party001_Main].[Control Start]) AS [MinOfControl Start], Max([3rd-Party001_Main].[Control Stop]) AS [MaxOfControl Stop]
FROM [3rd-Party001_Main]
GROUP BY [3rd-Party001_Main].[Record Type];

Answer 1

由于没有“family”标识符，因此仅有一个查询对象无法完成您想要的任务。当值发生变化时，需要VBA代码循环通过记录集检查，以确定“family”组的开始，设置StartAtt字段的值，并运行更新操作SQL以设置EndAtt字段。基于数据样本，考虑：

Sub SetRange()
Dim rs As DAO.Recordset, strStart As String, strEnd As String, strApp As String
Set rs = CurrentDb.OpenRecordset("SELECT * FROM [3rd-Party001_Main] WHERE BegAtt Is Null ORDER BY ControlStart;")
Do
    If rs!Application Like "Outlook*" Then
        strStart = rs!ControlStart
    End If
    strEnd = rs!ControlStop
    rs.Edit
    rs!StartAtt = strStart
    rs.Update
    rs.MoveNext
    If Not rs.EOF Then strApp = rs!Application
    If (Not rs.EOF And strApp Like "Outlook*") Or rs.EOF Then 
        CurrentDb.Execute "UPDATE [3rd-Party001_Main] SET EndAtt = '" & strEnd & "' WHERE StartAtt='" & strStart & "'"
    End If
Loop Until rs.EOF
End Sub

建议在命名约定中不要使用空格或标点符号/特殊字符。