Combine Multiple Class Rows into One Row per School in Microsoft Access
Question details
Combine each school's related class IDs and names into separate columns, with one row per school.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- A database contains multiple class records for each School_ID, and the user wants a flattened result for each school.
- Observed behavior
- The desired result has one row per school and paired columns such as Class_ID_1 and Class_Name_1.
Identify the table or query containing School_ID, Class_ID, and Class_Name. The example assumes Class_ID is non-null and unique within each school; adjust the names and ranking logic if your data differs.
Rank classes and create crosstab queries in Access
Use this approach to place class IDs and names in paired columns, with one row per school.
The example displays up to three classes per school, ordered by Class_ID. Replace Classes and the field names with those in your database. Schools without class records will not appear.
In Access, select Create > Query Design, switch to SQL View, and enter: SELECT C.School_ID, C.Class_ID, C.Class_Name, (SELECT Count(*) FROM Classes AS C2 WHERE C2.School_ID = C.School_ID AND C2.Class_ID <= C.Class_ID) AS ClassRank FROM Classes AS C; Save it as qryRankedClasses.
Create another query in SQL View and enter: TRANSFORM First(Class_ID) AS ClassValue SELECT School_ID FROM qryRankedClasses GROUP BY School_ID PIVOT ClassRank IN (1, 2, 3); Save it as qryClassIDs.
Create another query in SQL View and enter: TRANSFORM First(Class_Name) AS ClassValue SELECT School_ID FROM qryRankedClasses GROUP BY School_ID PIVOT ClassRank IN (1, 2, 3); Save it as qryClassNames.
Create a final query in SQL View and enter: SELECT I.School_ID, I.[1] AS Class_ID_1, N.[1] AS Class_Name_1, I.[2] AS Class_ID_2, N.[2] AS Class_Name_2, I.[3] AS Class_ID_3, N.[3] AS Class_Name_3 FROM qryClassIDs AS I INNER JOIN qryClassNames AS N ON I.School_ID = N.School_ID; Select Run to view the result.
Create a grouped Access report with a class subreport
Use a report instead of separate columns when the goal is to display or print each school's related classes.
Review exported school and class data in WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar interface for working with exported school data. Open compatible Office files to move spreadsheet work into WPS, while using Access to create the database queries.

Frequently Asked Questions
How does Access decide which class becomes Class_ID_1?
The ranking query orders classes by Class_ID within each school. The lowest Class_ID receives rank 1 and appears in Class_ID_1.
What if a school has more than three classes?
Add more ranks to the PIVOT IN lists in both crosstab queries, then add corresponding ID and name columns to the final SELECT query.
Can I include the school name in the result?
Yes. In Query Design, add the school table to the final query, join it to the crosstab result on School_ID, and add the school name field to the output.
Will schools with no classes appear in the crosstab result?
No. The example starts from class records. To include schools with no classes, use the school table in a final query with a left join to the crosstab results.
Can WPS Office create the Access crosstab queries?
Use Microsoft Access to create and run the database queries. You can export the results to an Excel file and open that file in WPS Spreadsheets.




