按字段拆分列并合并记录MS Access 2007

Question

我有以下查询，其中我根据字段“Required”将一列分解为不同的列。

SELECT 
Station, 
Line, 
[Tag Reference], 
FTN, 
IIF(Required = "TF", Required, "") AS [TF Required], 
IIF(Required = "TF", [Date Checked], "") AS [TF Date], 
IIF(Required = "TF", [User Checked], "") AS [TF User], 
IIF(Required = "ML", Required, "") AS [ML Required], 
IIF(Required = "ML", [Date Checked], "") AS [ML Date], 
IIF(Required = "ML", [User Checked], "") AS [ML User], 
Format

FROM [Tag Information]

ORDER BY 
Station, 
Line, 
[Tag Reference]

我正在获取项目，其中每条记录都有自己的行，但希望将这些记录分组在一起。

即。我得到了这个表，其中的行是拆分的，但我希望看到按标记引用分组的值。(类似于第二个表)

Station Line    Tag Reference   FTN TF Required TF Date TF User ML Required ML Date ML User Format
Clearbrook  1   SCADA[30].0                 ML  7/12/2006   WPB 
Clearbrook  1   SCADA[30].0     TF                      
Clearbrook  1   SCADA[30].1                 ML  7/12/2006   WPB 
Clearbrook  1   SCADA[30].1     TF                      
Clearbrook  1   SCADA[30].10                    ML  7/12/2006   WPB 
Clearbrook  1   SCADA[30].2                 ML  7/12/2006   WPB 
Clearbrook  1   SCADA[30].4                 ML  7/12/2006   WPB 
Clearbrook  1   SCADA[30].4     TF  7/12/2006   WPB             
Clearbrook  1   SCADA[30].5     TF  7/12/2006   WPB             
Clearbrook  1   SCADA[30].5                 ML  7/12/2006   WPB 
Clearbrook  1   SCADA[30].6                 ML  7/12/2006   WPB 
Clearbrook  1   SCADA[30].8                 ML  7/12/2006   WPB 
Clearbrook  1   SCADA[30].8     TF  7/12/2006   WPB             
Clearbrook  1   SCADA[30].9     TF  7/12/2006   WPB             
Clearbrook  1   SCADA[30].9                 ML  7/12/2006   WPB 

我想看看这张桌子：

    Station Line    Tag Reference   FTN TF Required TF Date TF User ML Required ML Date ML User Format
    Clearbrook  1   SCADA[30].0     TF          ML  7/12/2006   WPB 
    Clearbrook  1   SCADA[30].1     TF          ML  7/12/2006   WPB 
    Clearbrook  1   SCADA[30].10                    ML  7/12/2006   WPB 
    Clearbrook  1   SCADA[30].2                 ML  7/12/2006   WPB 
    Clearbrook  1   SCADA[30].4     TF  7/12/2006   WPB ML  7/12/2006   WPB 
    Clearbrook  1   SCADA[30].5     TF  7/12/2006   WPB ML  7/12/2006   WPB 
    Clearbrook  1   SCADA[30].6                 ML  7/12/2006   WPB 
    Clearbrook  1   SCADA[30].8     TF  7/12/2006   WPB ML  7/12/2006   WPB 
    Clearbrook  1   SCADA[30].9     TF  7/12/2006   WPB ML  7/12/2006   WPB 

任何帮助都是最好的。我不知所措。

Answer 1

您应该能够使用GROUP BY查询来合并结果，如下所示：

假设您有一个名为ThingStatus的表，其中包含数据...

ThingName  Status1  Status2
---------  -------  -------
foo        thing1          
foo                 thing2 

您可以使用查询合并条目...

SELECT 
    ThingStatus.ThingName, 
    Max(ThingStatus.Status1) AS Status1, 
    Max(ThingStatus.Status2) AS Status2
FROM ThingStatus
GROUP BY ThingStatus.ThingName;

...which为您提供

ThingName  Status1  Status2
---------  -------  -------
foo        thing1   thing2