如何更新BegAtt&通过MDB查询EndAtt值? [英] How to update BegAtt & EndAtt values by MDB query?

问题描述

我想更新BegAtt&根据查询，基于MDB中控制开始"，控制停止"，应用程序"和记录类型"字段的EndAtt值. 以下是我的MDB数据库的记录集，我要根据其中具有Record Type = Email的父记录来更新BegAtt和EndAtt值:

I want to update the BegAtt & EndAtt values based on the Control Start, Control Stop, Application, and Record Type fields in MDB by query. Below is the record set of my MDB database where I want to update BegAtt and EndAtt values based on the parent record which has Record Type = Email:

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值应为族的最后一个值(记录类型=电子邮件附件).以下是理想的结果:

The BegAtt value should be first value of main record (Record Type = Email) and EndAtt value should be last value of family (record Type = Email-Attachment). Below is the desired result:

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

我尝试了下面的代码，但结果不正确.

I tried below code which gets incorrect result.

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];

推荐答案

由于没有家庭"标识符，仅查询对象将无法完成您想要的工作.需要VBA代码遍历记录集检查，以检查值更改以确定家庭"组的开始，设置StartAtt字段的值以及运行UPDATE操作SQL来设置EndAtt字段的开始.根据数据样本，请考虑:

Since there is no 'family' identifier, a query object alone will not accomplish what you want. Need VBA code looping through recordset checking when values change to determine start of 'family' group, set value of StartAtt field, and run an UPDATE action SQL to set EndAtt field. Based on data sample, consider:

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

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

Advise not to use spaces nor punctuation/special characters in naming convention.

这篇关于如何更新BegAtt&通过MDB查询EndAtt值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！