Access数据库多方法提交数据规范化及可编辑查询实现问询

安全阿岳

2026-6-13

Access多测试方法记录拆分与可编辑查询需求

背景

我们用Access管理测试样品提交工作，现有18种可执行的测试方法，每种方法的测试次数由提交方决定。当前数据库设置为单记录仅支持一种方法录入，单样品需多种方法时需创建多条记录。我希望实现单记录多选方法（非规范化结构，便于提交方操作），再通过查询将数据转换为测试团队所需的规范化格式（按选中方法拆分记录）。

后端表结构说明

核心表Sample SubmissionSingleBatch包含以下类型字段：

基础信息字段：AutoID、Lab Batch ID、IRIS Batch ID、Requester、Samples等
多值选择字段：Method（包含18种测试方法选项）
方法对应属性字段：每个方法单独对应XXXRuns（测试次数）、XXXPriority（优先级）、XXXReceived/Started/Finished（状态时间）字段，例如CFPPLinearRuns、CFPPPriority等

期望的规范化数据格式

拆分后每条记录对应一个选中的方法，字段包含：
AutoID、Lab Batch ID、IRIS Batch ID、Requester、Samples、对应方法的Runs、Method名称、对应方法的Priority、Instrument Specific、Drop-Off date、Comments、Samples in Class 1 FC?、Flash Point of sample °C、对应方法的Received/Started/Finished、Retain?

当前实现的SQL查询

我用以下SQL实现了格式转换，但因调用自定义VBA函数，导致Runs、Priority等字段无法编辑：

SELECT [Sample SubmissionSingleBatch].AutoID, 
[Sample SubmissionSingleBatch].[Lab Batch ID], 
[Sample SubmissionSingleBatch].[IRIS Batch ID], 
[Sample SubmissionSingleBatch].Requester, 
[Sample SubmissionSingleBatch].Samples, 
GetRunsWanted([Method].[Value],[CFPPLinearRuns],[CFPPRuns],
[CFPP12Runs],[CloudMiniRuns],[CloudRuns],[Conf_CFPPRuns],
[Conf_CloudMiniRuns],[Conf_CloudRuns],[Conf_HFRRRuns],
[Conf_PourMiniRuns],[Conf_PourAutoRuns],[DistRuns],[HFRRRuns],
[PourMiniRuns],[PourAutoRuns],[Pour_MANRuns],[RancimatRuns],
[SFPPRuns]) AS Runs, 
[Sample SubmissionSingleBatch].Method.Value AS Method,
 GetPriorityWanted([Method].[Value],[CFPPLinearPriority],
[CFPPPriority],[CFPP12Priority],[CloudMiniPriority],[CloudPriority],
[Conf_CFPPPriority],[Conf_CloudMiniPriority],[Conf_CloudPriority],
[Conf_HFRRPriority],[Conf_PourMiniPriority],[Conf_PourAutoPriority],
[DistPriority],[HFRRPriority],[PourMiniPriority],[PourAutoPriority],
[Pour_MANPriority],[RancimatPriority],[SFPPPriority]) AS Priority, 
[Sample SubmissionSingleBatch].[Instrument Specific], 
[Sample SubmissionSingleBatch].[Drop-Off date], 
[Sample SubmissionSingleBatch].Comments, 
[Sample SubmissionSingleBatch].[Samples in Class 1 FC?], 
[Sample SubmissionSingleBatch].[Flash Point of sample °C],
 GetReceivedWanted([Method].[Value],[CFPPLinearReceived],
[CFPPReceived],[CFPP12Received],[CloudMiniReceived],[CloudReceived],
[Conf_CFPPReceived],[Conf_CloudMiniReceived],[Conf_CloudReceived],
[Conf_HFRRReceived],[Conf_PourMiniReceived],[Conf_PourAutoReceived],
[DistReceived],[HFRRReceived],[PourMiniReceived],[PourAutoReceived],
[Pour_MANReceived],[RancimatReceived],[SFPPReceived]) AS Received, 
GetStartedWanted([Method].[Value],[CFPPLinearStarted],[CFPPStarted],
[CFPP12Started],[CloudMiniStarted],[CloudStarted],[Conf_CFPPStarted],
[Conf_CloudMiniStarted],[Conf_CloudStarted],[Conf_HFRRStarted],
[Conf_PourMiniStarted],[Conf_PourAutoStarted],[DistStarted],
[HFRRStarted],[PourMiniStarted],[PourAutoStarted],[Pour_MANStarted],
[RancimatStarted],[SFPPStarted]) AS Started,
 GetFinishedWanted([Method].[Value],[CFPPLinearFinished],
[CFPPFinished],[CFPP12Finished],[CloudMiniFinished],[CloudFinished],
[Conf_CFPPFinished],[Conf_CloudMiniFinished],[Conf_CloudFinished],
[Conf_HFRRFinished],[Conf_PourMiniFinished],[Conf_PourAutoFinished],
[DistFinished],[HFRRFinished],[PourMiniFinished],[PourAutoFinished],
[Pour_MANFinished],[RancimatFinished],[SFPPFinished]) AS Finished, 
[Sample SubmissionSingleBatch].[Retain?]
FROM 
[Sample SubmissionSingleBatch];

自定义VBA函数示例（GetRunsWanted）

Function GetRunsWanted(MethodValue As String, CFPPLinearRuns As Variant, 
     CFPPRuns As Variant, CFPP12Runs As Variant, CloudMiniRuns As Variant, 
     CloudRuns As Variant, Conf_CFPPRuns As Variant, 
     Conf_CloudMiniRuns As Variant, Conf_CloudRuns As Variant, 
     Conf_HFRRRuns As Variant, Conf_PourMiniRuns As Variant,
     Conf_PourAutoRuns As Variant, DistRuns As Variant, 
     HFRRRuns As Variant, PourMiniRuns As Variant, 
     PourAutoRuns As Variant, Pour_MANRuns As Variant, 
     RancimatRuns As Variant, SFPPRuns As Variant) As Variant
    Select Case MethodValue
        Case "CFPP LINEAR/EN16329"
            GetRunsWanted = CFPPLinearRuns
        Case "CFPP/LQP055"
            GetRunsWanted = CFPPRuns
        Case "CFPP12"
            GetRunsWanted = CFPP12Runs
        Case "CLOUD MINI/ASTM D7689"
            GetRunsWanted = CloudMiniRuns
        Case "CLOUD/ASTM D5771"
            GetRunsWanted = CloudRuns
        Case "Conf_CFPP/LQP055"
            GetRunsWanted = Conf_CFPPRuns
        Case "Conf_CLOUD MINI/ASTM D7689"
            GetRunsWanted = Conf_CloudMiniRuns
        Case "Conf_CLOUD/ASTM D5771"
            GetRunsWanted = Conf_CloudRuns
        Case "Conf_HFRR/ISO12156"
            GetRunsWanted = Conf_HFRRRuns
        Case "Conf_POUR MPP/ASTM D7346"
            GetRunsWanted = Conf_PourMiniRuns
        Case "Conf_POUR_AUTO/ASTM D5950"
            GetRunsWanted = Conf_PourAutoRuns
        Case "DIST_D86"
            GetRunsWanted = DistRuns
        Case "HFRR/ISO12156"
            GetRunsWanted = HFRRRuns
        Case "POUR MPP/ASTM D7346"
            GetRunsWanted = PourMiniRuns
        Case "POUR_AUTO/ASTM D5950"
            GetRunsWanted = PourAutoRuns
        Case "POUR_MAN/ASTM D97"
            GetRunsWanted = Pour_MANRuns
        Case "RANCIMAT/LQP092"
            GetRunsWanted = RancimatRuns
        Case "SFPP"
            GetRunsWanted = SFPPRuns
        Case Else
            GetRunsWanted = Null
    End Select
End Function

问题

当前查询的格式完全符合需求，但因使用VBA函数，Runs、Priority等字段无法编辑。我知道可以直接修改原始表，但查询的界面更友好，想问有没有办法实现查询结果可编辑，或者这已经超出Access的能力范围？

解决方案

要让查询结果可编辑，核心是避免使用自定义VBA函数，改用Access原生支持的可更新查询语法，以下是两种可行方案：

方案1：用Switch函数替换VBA函数

Access的Switch函数可在SQL中直接实现条件判断，且不会破坏查询的可更新性。以Runs字段为例，替换后的SQL片段：

Switch(
    [Method].[Value] = "CFPP LINEAR/EN16329", [CFPPLinearRuns],
    [Method].[Value] = "CFPP/LQP055", [CFPPRuns],
    [Method].[Value] = "CFPP12", [CFPP12Runs],
    [Method].[Value] = "CLOUD MINI/ASTM D7689", [CloudMiniRuns],
    [Method].[Value] = "CLOUD/ASTM D5771", [CloudRuns],
    [Method].[Value] = "Conf_CFPP/LQP055", [Conf_CFPPRuns],
    [Method].[Value] = "Conf_CLOUD MINI/ASTM D7689", [Conf_CloudMiniRuns],
    [Method].[Value] = "Conf_CLOUD/ASTM D5771", [Conf_CloudRuns],
    [Method].[Value] = "Conf_HFRR/ISO12156", [Conf_HFRRRuns],
    [Method].[Value] = "Conf_POUR MPP/ASTM D7346", [Conf_PourMiniRuns],
    [Method].[Value] = "Conf_POUR_AUTO/ASTM D5950", [Conf_PourAutoRuns],
    [Method].[Value] = "DIST_D86", [DistRuns],
    [Method].[Value] = "HFRR/ISO12156", [HFRRRuns],
    [Method].[Value] = "POUR MPP/ASTM D7346", [PourMiniRuns],
    [Method].[Value] = "POUR_AUTO/ASTM D5950", [PourAutoRuns],
    [Method].[Value] = "POUR_MAN/ASTM D97", [Pour_MANRuns],
    [Method].[Value] = "RANCIMAT/LQP092", [RancimatRuns],
    [Method].[Value] = "SFPP", [SFPPRuns]
) AS Runs

将所有VBA函数调用替换为对应的Switch语句，即可保持查询的可更新性——因为Switch是Access SQL原生函数，不会阻断字段与原始表的关联。

方案2：创建方法映射表+联合查询

创建MethodMapping表，包含MethodName（方法名称）、RunsField（对应Runs字段名）、PriorityField（对应Priority字段名）等字段，存储每个方法对应的原始表字段名。
用联合查询拆分多值字段，再通过预写的分支关联对应字段。该方案复杂度较高，适合方法数量可能变动的场景。

注意事项

Access的多值字段属于特殊集合类型，拆分后的查询要保持可更新，需确保每条拆分记录能唯一关联回原始表的对应字段。
使用Switch函数时，需覆盖所有方法选项，未匹配的情况会返回Null，与原VBA函数逻辑一致。

内容的提问来源于stack exchange，提问作者Ben

Access数据库多方法提交数据规范化及可编辑查询实现问询

背景 #

后端表结构说明 #

期望的规范化数据格式 #

当前实现的SQL查询 #

自定义VBA函数示例（GetRunsWanted） #

问题 #

解决方案 #