MS Access Query -Transpose Data

[bbcdancer]

Can this be done by SQL query in MS Access or do I need VBA to do this?

From Table 1 to Table 2, the 'Name' column will contain over 20k names and the 'Product' column is fixed at 18 products.

(I used the | pipe as a seperator in the below tables, but this is only to illustrate how the table would look like.)

Table 1: Before

Name | Product
----------------------
Rob | AAA
Rob | BBB
Jane | BBB
Rob | CCC
Fred | EEE
Jack | AAA
Rob | DDD
MIKE | CCC
MIKE | DDD
......


Table 2: After

Name | AAA | BBB | CCC | DDD | EEE | ...TTT
-----------------------------------------------
Rob | AAA | BBB | CCC | DDD |
Jane | | BBB | | |
Fred | | | | | EEE
Jack | AAA | | | |
Mike | | | CCC | DDD |
.......

Any help is appreciated.

 

[Trevor G]

Try looking at a CrossTab query to see if that will do what you want.

 

[SydneyGeek]

Following up Trevor's suggestion

Build a query with 3 fields (Name once, Product twice).
Change it to a Crosstab (Query > Crosstab, or for 2007/2010, Design tab > Crosstab).
In the Orientation row, make the fields Row heading, Column heading, Value.
In the Totals row, leave the first two fields as Group By, and make the third Max.

Denis