Combine multiple records into one row with multiple columns [SOLVED]

[postman]

I need to combine multiple records into one row with multiple columns for those records. Very similar to a crosstab query, but one that shows all the records, not a summary of them.

For example, if the table is like this:

Name     Month     Score
John     Jan       92
John     Jan       90
John     Feb       97
John     Feb       91

I want a query that gives these results like this:

Month     Name     Score1     Score2
Jan       John     92         90
Feb       John     97         91

Crosstab queries are set for summarizing data, but I don't want that--I just want ALL the scores laid out in a row for each person for each month.

I'm using Access 2007 and in this case, VBA is not an option.

Any help would be much appreciated.

EDIT: See complete solution down below.

[Seth Schrock]

Can there be more than two scores per person per month? There is a SQL solution for that, but the more scores, the harder it is to code.

[postman]

It would be a maximum of 4 scores.

[Seth Schrock]

Just an FYI, it is not a good idea to use Name or Month as field names as those are reserved words in Access. Access does permit you to use them, but it can sometimes cause problems.

Anyway, we first need to have an id field in your table. An autonumber field would be perfect for this. Then we need to create a querydef that puts a number beside each record to tell us if it is score 1, 2, 3 or 4. I'll assume a table name of tblScores.

SELECT ID
, [Name]
, [Month]
, Score
, DCount("*"
    , "tblScores"
    , "[Name] = '" & [Name] & 
        "' And [Month] = '" & [Month] & 
        "' And ID <= " & ID) 
    As ScoreNum
FROM tblScores

This should produce the results

ID    Name    Month    Score    ScoreNum
1     John    Jan      92       1
2     John    Jan      90       2
3     John    Feb      97       1
4     John    Feb      91       2

Save this query as qryScores. Now the query that gets the results that you want will have several subqueries to get the score numbers based on the ScoreNum field.

SELECT MQ.ID
, MQ.[Name]
, MQ.[Month]
, S1.Score As Score1
, S2.Score As Score2
, S3.Score As Score3
, S4.Score As Score4
FROM qryScores As MQ 
LEFT JOIN (SELECT ID, Score FROM qryScores WHERE ScoreNum = 1) As S1 ON MQ.ID = S1.ID
LEFT JOIN (SELECT ID, Score FROM qryScores WHERE ScoreNum = 2) As S2 ON MQ.ID = S2.ID
LEFT JOIN (SELECT ID, Score FROM qryScores WHERE ScoreNum = 3) As S3 ON MQ.ID = S3.ID
LEFT JOIN (SELECT ID, Score FROM qryScores WHERE ScoreNum = 4) As S4 ON MQ.ID = S4.ID

[jforbes]

Nice Work! That is probably preferable to a Crosstab as you will have a predefined amount of columns.

But if you wanted to, since you already did the hard work of creating a RowNumber type column, you could use a Crosstab ;)

TRANSFORM First(qryScores.[Month]) AS Scores
SELECT qryScores.[Name], First(qryScores.[Month]) AS ReportMonth
FROM qryScores
GROUP BY qryScores.[Name], qryScores.[Month]
PIVOT qryScores.[ScoreNum]

[postman]

Thanks, Seth.

I know about reserved names and ID fields. It was just a simple example to illustrate what I was after.

I actually tried this method with the DCount function before, but it ran extremely slow due to the aggregate function. But I see a few differences in your function's criteria that I'll try out.

I'll test and post results.

Thanks!

[Seth Schrock]

The only way that you can speed up the query is to reduce the number of records that it has to run the aggregate function on.

[NeoPa]

Actually Seth, you could link in the data as a separate data source rather than use a Domain Aggregate function call. The latter are notoriously inefficient and should be avoided where possible within SQL.

As J Forbes illustrates this can still be done using a cross-tab. I would expect that to work more efficiently as you're letting the SQL engine to the hard work - which is what it's designed for.

[postman]

NeoPa, could you please clarify or give an example of linking in the data as a separate data source?

Thanks.

[postman]

Seth, the performance is much better on the DCount function using the method in your example, thank you.

But I keep getting a "syntax error (missing operator)" on the second query example you gave with the subqueries. The error is in the section with the LEFT JOINS.

Any ideas?

[Seth Schrock]

Try this

SELECT MQ.ID
, MQ.[Name]
, MQ.[Month]
, S1.Score As Score1
, S2.Score As Score2
, S3.Score As Score3
, S4.Score As Score4
FROM ((((qryScores As MQ 
LEFT JOIN (SELECT ID, Score FROM qryScores WHERE ScoreNum = 1) As S1 ON MQ.ID = S1.ID)
LEFT JOIN (SELECT ID, Score FROM qryScores WHERE ScoreNum = 2) As S2 ON MQ.ID = S2.ID)
LEFT JOIN (SELECT ID, Score FROM qryScores WHERE ScoreNum = 3) As S3 ON MQ.ID = S3.ID)
LEFT JOIN (SELECT ID, Score FROM qryScores WHERE ScoreNum = 4) As S4 ON MQ.ID = S4.ID)

Access sometimes doesn't like multiple joins without the parenthesis.

[postman]

Thanks.
That eliminated that error, but now it's prompting for a Score parameter when I run the query.

[Seth Schrock]

Can you copy and paste into this thread what you have just in case there is an error somewhere?

[postman]

SELECT MQ.qID
, MQ.[sMonth]
, MQ.[rName]
, MQ.MgrName
, S1.Score AS [Score 1]
, S2.Score AS [Score 2]
, S3.Score AS [Score 3]
, S4.Score AS [Score 4]
FROM (((([ScoreCount] AS MQ 
LEFT JOIN (SELECT qID, Score FROM [ScoreCount] WHERE ScoreNum = 1) As S1 ON MQ.qID = S1.qID) 
LEFT JOIN (SELECT qID, Score FROM [ScoreCount] WHERE ScoreNum = 2) As S2 ON MQ.qID = S2.qID) 
LEFT JOIN (SELECT qID, Score FROM [ScoreCount] WHERE ScoreNum = 3) As S3 ON MQ.qID = S3.qID) 
LEFT JOIN (SELECT qID, Score FROM [ScoreCount] WHERE ScoreNum = 4) As S4 ON MQ.qID = S4.qID);

[Seth Schrock]

Well, nothing is obviously wrong. Try removing lines 6 through 8 and see if you get an error. Keep adding lines one at a time until you do receive the error and then let us know which one errors out.