Others

Combine Multiple Class Rows into One Row per School in Microsoft Access

Jason MillerJason Miller Nov 18, 2026 869 views

Question details

Combine each school's related class IDs and names into separate columns, with one row per school.

Combine Multiple Class Rows into One Row per School in Microsoft Access
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.
Before you start

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.

Solution 1Recommended

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.

1
Create a ranking query

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.

2
Create the class ID crosstab

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.

3
Create the class name crosstab

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.

4
Join the crosstab results

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.

Allow for more classes: Classes ranked above 3 are not shown. Add the required ranks to the IN list in both crosstab queries and add matching ID and name columns to the final query.
Free Microsoft Office alternative

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.

Review and format exported school and class columns in WPS SpreadsheetsOpen and edit Microsoft Excel XLSX filesUse a familiar interface for everyday spreadsheet workMove existing compatible Office files into WPS Office
QA img-9

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.