convert data from multiple rows to multiple fields Access 2007

Question

My table where I am storing my information is organized as follows:

ID JobID Project FirstName LastName

One job may have multiple projects and within the projects there may be up to 5 contacts. I would like to display the information in the following way:

ID JobID Project FirstName1 LastName1 FirstName2 LastName2.........FirstName5 LastName 5

Can a transform query be used for this or do you need to create another table and insert the information?

Thanks

Answer 1

Yes, what you describe is feasible. For test data in a table named [contactInfo]

ID  JobID  Project  FirstName  LastName
--  -----  -------  ---------  --------
 1      1        1  Homer      Simpson 
 2      1        1  Marge      Simpson 
 3      1        1  Lisa       Simpson 
 4      1        2  Ned        Flanders

start by creating a saved query in Access named [contactInfoRanked]

SELECT contactInfo.*, ranks.rowRank
FROM 
    contactInfo
    INNER JOIN
    (
        SELECT t1.ID, Count(*) AS rowRank
        FROM 
            contactInfo AS t1 
            INNER JOIN 
            contactInfo AS t2
                ON t1.JobID = t2.JobID 
                    AND t1.Project = t2.Project 
                    AND t1.ID >= t2.ID
        GROUP BY t1.ID
    ) AS ranks
        ON contactInfo.ID = ranks.ID

which returns

ID  JobID  Project  FirstName  LastName  rowRank
--  -----  -------  ---------  --------  -------
 1      1        1  Homer      Simpson         1
 2      1        1  Marge      Simpson         2
 3      1        1  Lisa       Simpson         3
 4      1        2  Ned        Flanders        1

So now the query

    SELECT 
        JobID, 
        Project, 
        "FirstName1" AS columnName,
        FirstName AS columnValue
    FROM contactInfoRanked
    WHERE rowRank = 1
UNION ALL
    SELECT 
        JobID, 
        Project, 
        "LastName1" AS columnName,
        LastName AS columnValue
    FROM contactInfoRanked
    WHERE rowRank = 1
UNION ALL
    SELECT 
        JobID, 
        Project, 
        "FirstName2" AS columnName,
        FirstName AS columnValue
    FROM contactInfoRanked
    WHERE rowRank = 2
UNION ALL
    SELECT 
        JobID, 
        Project, 
        "LastName2" AS columnName,
        LastName AS columnValue
    FROM contactInfoRanked
    WHERE rowRank = 2
UNION ALL
    SELECT 
        JobID, 
        Project, 
        "FirstName3" AS columnName,
        FirstName AS columnValue
    FROM contactInfoRanked
    WHERE rowRank = 3
UNION ALL
    SELECT 
        JobID, 
        Project, 
        "LastName3" AS columnName,
        LastName AS columnValue
    FROM contactInfoRanked
    WHERE rowRank = 3
UNION ALL
    SELECT 
        JobID, 
        Project, 
        "FirstName4" AS columnName,
        FirstName AS columnValue
    FROM contactInfoRanked
    WHERE rowRank = 4
UNION ALL
    SELECT 
        JobID, 
        Project, 
        "LastName4" AS columnName,
        LastName AS columnValue
    FROM contactInfoRanked
    WHERE rowRank = 4
UNION ALL
    SELECT 
        JobID, 
        Project, 
        "FirstName5" AS columnName,
        FirstName AS columnValue
    FROM contactInfoRanked
    WHERE rowRank = 5
UNION ALL
    SELECT 
        JobID, 
        Project, 
        "LastName5" AS columnName,
        LastName AS columnValue
    FROM contactInfoRanked
    WHERE rowRank = 5

returns

JobID  Project  columnName  columnValue
-----  -------  ----------  -----------
    1        1  FirstName1  Homer      
    1        2  FirstName1  Ned        
    1        1  LastName1   Simpson    
    1        2  LastName1   Flanders   
    1        1  FirstName2  Marge      
    1        1  LastName2   Simpson    
    1        1  FirstName3  Lisa       
    1        1  LastName3   Simpson    

and we can just use that as the source for our crosstab query

TRANSFORM First(columnValue) AS colVal
SELECT JobID, Project
FROM
    (
            SELECT 
                JobID, 
                Project, 
                "FirstName1" AS columnName,
                FirstName AS columnValue
            FROM contactInfoRanked
            WHERE rowRank = 1
        UNION ALL
            SELECT 
                JobID, 
                Project, 
                "LastName1" AS columnName,
                LastName AS columnValue
            FROM contactInfoRanked
            WHERE rowRank = 1
        UNION ALL
            SELECT 
                JobID, 
                Project, 
                "FirstName2" AS columnName,
                FirstName AS columnValue
            FROM contactInfoRanked
            WHERE rowRank = 2
        UNION ALL
            SELECT 
                JobID, 
                Project, 
                "LastName2" AS columnName,
                LastName AS columnValue
            FROM contactInfoRanked
            WHERE rowRank = 2
        UNION ALL
            SELECT 
                JobID, 
                Project, 
                "FirstName3" AS columnName,
                FirstName AS columnValue
            FROM contactInfoRanked
            WHERE rowRank = 3
        UNION ALL
            SELECT 
                JobID, 
                Project, 
                "LastName3" AS columnName,
                LastName AS columnValue
            FROM contactInfoRanked
            WHERE rowRank = 3
        UNION ALL
            SELECT 
                JobID, 
                Project, 
                "FirstName4" AS columnName,
                FirstName AS columnValue
            FROM contactInfoRanked
            WHERE rowRank = 4
        UNION ALL
            SELECT 
                JobID, 
                Project, 
                "LastName4" AS columnName,
                LastName AS columnValue
            FROM contactInfoRanked
            WHERE rowRank = 4
        UNION ALL
            SELECT 
                JobID, 
                Project, 
                "FirstName5" AS columnName,
                FirstName AS columnValue
            FROM contactInfoRanked
            WHERE rowRank = 5
        UNION ALL
            SELECT 
                JobID, 
                Project, 
                "LastName5" AS columnName,
                LastName AS columnValue
            FROM contactInfoRanked
            WHERE rowRank = 5
    )
GROUP BY JobID, Project
PIVOT columnName 
    IN 
        (
            "FirstName1","LastName1",
            "FirstName2","LastName2",
            "FirstName3","LastName3",
            "FirstName4","LastName4",
            "FirstName5","LastName5"
        )

which returns

JobID  Project  FirstName1  LastName1  FirstName2  LastName2  FirstName3  LastName3  FirstName4  LastName4  FirstName5  LastName5
-----  -------  ----------  ---------  ----------  ---------  ----------  ---------  ----------  ---------  ----------  ---------
    1        1  Homer       Simpson    Marge       Simpson    Lisa        Simpson                                                
    1        2  Ned         Flanders