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

[postman]

I tried that and I still get the prompt to enter a Parameter Value for Score.

I also removed lines 10-12 but still get the prompt.

Any other ideas?

[Seth Schrock]

It sounds like the field name Score isn't accurate.

[postman]

Okay, it was my fault. Had the Score field name wrong. Fixed that and I don't get the prompt, but it's not giving the expected results.

It's just listing the first score and showing a different row for each record still.

And the final query runs extremely slow.

Any ideas? I really appreciate your help on this.

[Seth Schrock]

Did you start back with the query from post #14 and fix the score field name? As NeoPa and JForbes said, a cross tab might be better since you have the ScoreNum field.

[postman]

Yes fixed the Score field name.

Trying the crosstab again. I'll post results.

Do you know what NeoPa meant by "link in the data as a separate data source rather than use a Domain Aggregate function call" ?

Sounds like it might help the performance on the ScoreCount query that is getting the ScoreNum numbering.

[Seth Schrock]

You would create another subquery and use the Count() function. Try the following for your ScoreCount query:

SELECT T1.qID
, T1.sMonth
, T1.rName
, T1.MgrName
, T1.Score
, Count(T2.*) As ScoreNum
FROM tblScores As T1 LEFT JOIN
    (SELECT * FROM tblScores) As T2 
    ON T1.rName = T2.rName AND T1.sMonth = T2.sMonth

[postman]

I get this error when saving the query: "Syntax error in query expression 'Count(T2.*)'"

So I tried putting a field name: 'Count(T2.qID)' and that got past the error.

But when I ran the query, I got an error saying it did not include the specified expression 'qID' as part of an aggregate function--in reference to the qID in the SELECT statement at the beginning.

I really appreciate the help on this because I really want this to work. Using the other way with the DCount takes 45 seconds just to open that query, so I'm hoping the performance with this method can cut that down.

[postman]

I added GROUP BY to the end and that solved the error. But it's just showing a total count for each person for each month on each row--not the incremental count.

Here's the query now:

SELECT T1.qID
, T1.sMonth
, T1.rName
, T1.MgrName
, T1.Score
, Count(T2.qID) As ScoreNum
FROM tblScores As T1 LEFT JOIN
    (SELECT * FROM tblScores) As T2 
    ON T1.rName = T2.rName AND T1.sMonth = T2.sMonth
GROUP BY T1.qID, T1.sMonth, T1.rName, T1.MgrName, T1.qScore

Produces results like this:

qID  sMonth  rName    MgrName    Score  ScoreNum
1    Jan     John     George     92     3
2    Jan     John     George     94     3
3    Jan     John     George     98     3
4    Feb     John     George     97     2
5    Feb     John     George     91     2

[Seth Schrock]

Try removing the qID field from both the SELECT and GROUP BY clauses.

***Edit*** The above won't work. The Count function needs modified to only get records with lesser qID values that have matching sMonth and rName fields. I'm looking into this.

[Seth Schrock]

So ignore the fix from the previous post. Try adding the following to the end of line 9 of your last code:

 AND T1.qID <= T2.qID

[postman]

Solved

That did it! And the performance is WAY better.

Thank you, Seth!

Here is the complete solution:

Step 1: Created a SourceCount query to provide numbered results for each person in each time period:

SELECT T1.qID
, T1.sMonth
, T1.rName
, T1.MgrName
, T1.Score
, Count(T2.qID) As ScoreNum
FROM tblScores As T1 LEFT JOIN
    (SELECT * FROM tblScores) As T2 
    ON T1.rName = T2.rName AND T1.sMonth = T2.sMonth AND T1.qID <= T2.qID 
GROUP BY T1.qID, T1.sMonth, T1.rName, T1.MgrName, T1.qScore

Which gives results like this:

qID  sMonth  rName    MgrName    Score  ScoreNum
1    Jan     John     George     92     2
2    Jan     John     George     94     3
3    Jan     John     George     98     1
4    Feb     John     George     97     1
5    Feb     John     George     91     2

Step 2: Create a crosstab query:

TRANSFORM First([ScoreCount].qScore) AS Score
SELECT First([ScoreCount].[sMonth]), [ScoreCount].[rName], [ScoreCount].MgrName
FROM ScoreCount
GROUP BY [ScoreCount].[sMonth], [ScoreCount].[rName], [ScoreCount].MgrName
PIVOT [ScoreCount].[ScoreNum];

Which gives the desired final result:

sMonth  rName    MgrName    Score1  Score2  Score3  
Jan     John     George     98      92      94
Feb     John     George     97      91

Thanks again for everyone's help!

[Seth Schrock]

Glad you got it to work. Good luck on your project.

[NeoPa]

Congratulations Seth. Good work.

@Postman.
Please accept my apologies for leaving you without my response for so long. Work is keeping me very busy ATM and I don't get to visit as often as I'd like to. This is one I would have enjoyed too.

Luckily our Seth is getting more and more experienced and was able to resolve it for you.