The Problem (Q-score 4, ranked #32nd of 67 in the Access VBA archive)
The scenario as originally posted in 2009
What’s the equivalent of an MS-Access crosstab query in TSQL? And Is there a better way?
I have a data organised like this:
Fish
ID Name
---- ---------
1 Jack
2 Trout
3 Bass
4 Cat
FishProperty
ID FishID Property Value
---- ------ -------- -----
1 1 Length 10
2 1 Girth 6
3 1 Weight 4
4 2 Length 6
5 2 Weight 2
6 3 Girth 12
I have a number of users who need to do reporting on the data and (obviously) it would be easier for them if they could see it like this:
Fish
ID Name Length Girth Weight
---- --------- ------ ----- ------
1 Jack 10 6 4
2 Trout 6 2
3 Bass 12
My plan was to create a crosstab-like view that they could report on directly.
Why community consensus is tight on this one
Across 67 Access VBA entries in the archive, the accepted answer here holds solid answer (above median) status — meaning voters are unusually aligned on the right fix.
The Verified Solution — solid answer (above median) (+9)
11-line Access VBA pattern (copy-ready)
Are you looking for PIVOT?
Edit: You may have to get to the second page before you see the usage of the PIVOT syntax.
Edit 2: Another example.
Example:
SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles
FROM
(SELECT SalesPerson, Product, SalesAmount
FROM ProductSales ) ps
PIVOT
(
SUM (SalesAmount)
FOR Product IN
( [Oranges], [Pickles])
) AS pvt
Edit 3 CodeSlave, take a look at this blog entry for some more information concerning dynamic pivot queries.
When to Use It — vintage (14+ years old, pre-2013)
Ranked #32nd in its category — specialized fit
This pattern sits in the 71% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the Access VBA archive for a higher-consensus alternative.
What changed between 2009 and 2026
The answer is 17 years old. The Access VBA object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.