MS Access 2019查询技术求助：基于零件编号将多列特征数据合并为单行记录

I get exactly what you're stuck on—your current query maps each feature to its own column but leaves most values empty per row, and you need to collapse those scattered rows into a single complete record per Part&Des. Let's solve this with aggregate functions in Access SQL:

The Core Fix Idea #

Your existing query already handles the hard part of routing each Feature Description to the correct column. The missing piece is grouping by Part&Des and using an aggregate function (like Max()) to pull the non-empty value from each column for the group. Since each group only has one valid value per feature column, Max() will ignore empty strings and return exactly the value you need.

Modified Working Query #

SELECT 
    sub.[Part&Des],
    Max(sub.[Socket Colour]) AS [Socket Colour],
    Max(sub.[Supplementary Article/Info 2]) AS [Supplementary Article/Info 2],
    Max(sub.[Sensor Type]) AS [Sensor Type],
    Max(sub.[Connector Shape]) AS [Connector Shape],
    Max(sub.[Fitting Depth]) AS [Fitting Depth],
    Max(sub.[Cable Length]) AS [Cable Length]
FROM (
    -- Your original feature-mapping logic remains intact here
    SELECT 
        Data_Extractions.[Part&Des],
        IIf([Feature Description]="Socket colour",[Feature],"") AS [Socket Colour],
        IIf([Feature Description]="Supplementary Article/Info 2",[Feature],"") AS [Supplementary Article/Info 2],
        IIf([Feature Description]="Sensor Type",[Feature],"") AS [Sensor Type],
        IIf([Feature Description]="Connector Shape",[Feature],"") AS [Connector Shape],
        Replace(IIf([Feature Description]="Fitting Depth [mm]",[Feature],"-") & "mm","-mm","") AS [Fitting Depth],
        Replace(IIf([Feature Description]="Cable Length [mm]",[Feature],"-") & "mm","-mm","") AS [Cable Length]
    FROM Data_Extractions
    WHERE 
        Data_Extractions.[Part&Des] Like "VOL40001ABS" 
        AND Data_Extractions.Suppl Like "VXP"
) AS sub
GROUP BY sub.[Part&Des];

Breakdown of How It Works #

1. Subquery: The inner block is your original code—it takes raw data and assigns each feature value to its dedicated column, leaving others empty. We alias this as sub to reference it in the outer query.
2. Grouping & Aggregation: The outer query groups all rows by Part&Des, then uses Max() on each feature column. In Access, empty strings are treated as lower priority than actual text/numbers, so Max() will automatically pick the single non-empty value for each column in the group.

Quick Alternative #

If you prefer, you can swap Max() for First()—it will work just as well here. Access will grab the first non-empty value it finds for each column in the group, which gives the same result for your use case.

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