Crosstab/Pivot

[seat172]

This keeps coming back to haunt me so any help would be appreciated. I have one table, Dbo_tblParts_Other, the fields i want are Partcode, Alt_Partcode, and Supplier. The data is stored thus:

Partcode Alt_Partcode Supplier
AM9026 81459634 Volvo
AM9026 85422266 Volvo
AM9026 88456317 Volvo

I want to pivot this to be as below, there may be 10 Alt_Partcodes or 1, I intend to do this by supplier so the number of Suppliers is not important.

Partcode Volvo Volvo Volvo
AM9026 81459634 85422266 88456317

My Sql and VBA skills are none existent so any help would be great, and need to be explained simply

 

[jal]

I'm not sure that your request fits into a crosstab/pivot schema. Possibly what you want is concatenation. And I doubt that Access (unless you use VBA) is very handy with concatenation (even with Sql Server, concatenation is not a simply query).

Pivoting means to take a column of values (a vertical set of values) and rotate them to serve as column headers, with a tally/count in the main region. Thus you might start with this:

Partcode Alt_Partcode Supplier
AM9026 81459634 Volvo
AM9026 85422266 Volvo
AM9026 88456317 Volvo

and end up with these column names:

Partcode 81459634 85422266 88456317
AM9026.....1..............1..............1.......... (tally)
VV4025.....0..............0...............0.......... (tally)

"Concatenation" in the broad sense means joining words, but can also be used in the narrower sense of transforming a column of values into a single cell like this (here we use commas but spaces would be fine as well):

Partcode.............Alt_Partcodes
AM9026............. 81459634, 85422266, 88456317

So the first thing you need to decide is whether your situation calls for a pivot/crosstab on the one hand, or a concatenation on the other. To help you decide, I'll attach a cutout from my personal notebook (you can use it as a tutorial on pivot tables).