CODEWITHSUNDEEP
sqlsplitgroup-byms-access-2007
brans041
brans041

Reputation: 1

Split columns by field and combine records MS Access 2007

I have the following Query where I am breaking up one column into different columns based on the field "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]

I am getting items where each record is its own line, but would like to group the records together.

IE. I am getting this table where the rows are split, but I would like to see values that are grouped by the Tag Reference. (Like the second table)

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

I would like to see this table:

    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

Any Help would be great. I am at a loss.

Upvotes: 0

Views: 109

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123654

You should be able to use a GROUP BY query to consolidate the results, something like the following:

Say you have a table named [ThingStatus] with the data...

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

the you can consolidate the entries using the query...

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

...which gives you

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

Upvotes: 1

Related Questions