combining similar records to a single record in MS Access

Question

I'm working MS Access 2016, on a table that has student results: So the fields are simply studentID, Test, and Score. For reporting purposes, I need to generate a CSV file that has a student's TestScore values all in one row. So if I had:

StudentID:   Test:       TestScore:
A123         TestA       80
A123         TestB       90
B123         TestA       70
B123         TestB       95

How do I generate a table for export that looks like:

StudentID:   TestA:      TestB:
A123         80          90
B123         70          95

I don't think crosstabs would work because not all students in the table have taken all the same tests. And there are several thousand cases. I also have come to understand that may not be possible via SQL in MS Access.

Many thanks in advance for any helpful advice.

Answer 1

So thank you to Andre and Alex for guiding me to this solution, which works for the original question:

Table name here is "Output".

TRANSFORM Last(Output.[TestScore]) AS LastOfTestScore
SELECT Output.[StudentID]
FROM [Output]
GROUP BY Output.[StudentID]
PIVOT Output.[Test];

For someone else searching for a similar solution, note that I used "Last" instead of something else (you might want First, Count, etc.).

Answer 2

You can add the ColumnHeadings property of the Crosstab Query, include all tests - "TestA";"TestB";...

Are there only a set number or could this grow?