Convert Score Records into Columns with a Microsoft Access Crosstab Query
Question details
Convert multiple score records for each ID into sequential columns using 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.
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.
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.
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;
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.
Create another query in SQL View and enter: TRANSFORM Max(TransactionAmount) SELECT CustomerID FROM qryNumberRowsByGroup_Join GROUP BY CustomerID PIVOT RowNumber;
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.

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.

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.




