Others

Convert Score Records into Columns with a Microsoft Access Crosstab Query

Ayan MasoodAyan Masood Nov 18, 2026 870 views

Question details

Convert multiple score records for each ID into sequential columns using a Microsoft Access crosstab query.

Convert Score Records into Columns with a Microsoft Access Crosstab Query
Product
Microsoft Access
Device & OS
not provided
Scenario
A database contains multiple score records per customer, and the user wants one row per customer with a separate column for each score.
Observed behavior
Using test dates as crosstab column headings creates many date-based columns instead of sequential score columns.
Before you start

Identify the ID and score fields, plus an ordering field such as a test date and a unique record ID to break ties. The example assumes these ordering fields and the customer ID are not null; replace its table and field names with your own.

Solution 1Recommended

Number records within each ID and build a crosstab query

Use this method to display each customer's scores in numbered columns rather than creating a separate column for every test date.

Access tables have no inherent row order. The numbering query counts records in date order for each customer, using ScoreID to distinguish records with the same date.

The example uses a table named Scores with CustomerID, TransactionAmount, TestDate, and ScoreID fields. TransactionAmount represents the score or other value to display.

1
Create the numbering query

In Access, select Create > Query Design, then switch to SQL View. Enter: SELECT t.CustomerID, t.TransactionAmount, (SELECT Count(*) FROM Scores AS s WHERE s.CustomerID = t.CustomerID AND (s.TestDate < t.TestDate OR (s.TestDate = t.TestDate AND s.ScoreID <= t.ScoreID))) AS RowNumber FROM Scores AS t;

2
Save and check the numbering

Save the query as qryNumberRowsByGroup_Join, then select Run or switch to Datasheet View. Confirm that RowNumber starts at 1 for each customer and increases in the intended order.

3
Create the crosstab

Create another query in SQL View and enter: TRANSFORM Max(TransactionAmount) SELECT CustomerID FROM qryNumberRowsByGroup_Join GROUP BY CustomerID PIVOT RowNumber;

4
Run and review the result

Select Run to show one row per CustomerID, with columns headed 1, 2, 3, and so on. Save the query when the values appear in the expected order.

Number records within each ID and build a crosstab query
Choose an appropriate aggregate: Max returns the value for each customer and row number when that combination identifies one record. If your numbering is not unique, Max will combine matching records; correct the ordering or choose an aggregate appropriate to your data.
Free Microsoft Office alternative

Review Access crosstab results in WPS Spreadsheets

WPS Office is a free, lightweight Office alternative with a familiar interface and compatibility with common Microsoft Office formats. Export the Access query results to Excel, then open them in WPS Spreadsheets for a convenient transition to spreadsheet review. WPS does not run Access crosstab queries.

Review exported crosstab results in a spreadsheetOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interfaceWork with a free, lightweight Office alternative
microsoft office alternative - wps office

Frequently Asked Questions

Why does using the test date create so many columns?

A crosstab creates a column for each distinct pivot value. Pivoting on TestDate therefore creates date-based headings; pivot on a sequence number instead to get headings such as 1, 2, and 3.

What happens if two scores have the same test date?

Use a unique record field such as ScoreID as a tie-breaker in the numbering query. The example compares ScoreID when TestDate values are equal, giving each record a distinct position.

Can I limit the crosstab to four score columns?

Yes. In the crosstab's SQL View, replace PIVOT RowNumber with PIVOT RowNumber IN (1,2,3,4). Records numbered beyond 4 will not appear in those columns.

How can I open the crosstab results in WPS?

In Access, select the saved crosstab query and use External Data > Excel to export its results. Open the exported Excel file in WPS Spreadsheets; the export does not transfer the Access query itself.