Pivot

[seat172]

Hi, I have one table called MVPR and want to extract from it the part number and suppliers for that part. I want the layout to be :
Part No Supplier1 Supplier2 Supplier3
a1234 41758 41963 42531

However the data is in this form:
Part No Supplier
a1234 41758
a1234 41963
a1234 42531
The data i want has a prefix field as the selection criteria for this is "c".

I need to pivot the data around to get this layout but have no idea how to do it. I know there is a pivot statement in SQL but i'm only just starting down the SQL road and could do with the answer to this one a bit quicker than my learning curve allows....

Thanks for any help

 

[JamesMcS]

It looks like you'll have to combine a number of queries - one with part number and supplier1, another with part number and supplier2 and so on. You'll also need a table with all the part numbers in it.

Then, create a query with all the above in it. Left join your part number table to the other queries, pull down the relevant fields and you'll get what you need.

But, if possible I'd redesign the table with Part Number, Supplier Rank (for lack of a better term) and supplier number so your table looks like this:

P/N, S_Rank, S_Number
a1234, 1, 41578
a1234, 2, 41963....

That way your queries will be much easier to organise and run.

 

[seat172]

Thanks this table is a mess, it's possible to have 20 suppliers for some items though some will only have 1. They are stored in no particular order and are not ranked in any way. So i thought a pivot table, once i've established the maximum number of suppliers for a part and so the maximum number of columns. Whould be a neater way of extracting the data?

Thanks

 

[JamesMcS]

Ah - yeah it's always a problem when you're adding common fields into a table like this. Problem with pivoting (crosstabs in Access) is that usually you can only have row headings, one column heading (supplier in this case) and one value.

If the maximum is 20 you could go with the method above, but then what if a part number has a 21st supplier? That's the problem...

Rank was a poor choice of word - I just wanted to differentiate between supplier number and supplier..... ID if you will

I think the best way would be, if you can, to redesign the table. You'll find it a lot simpler in the long run.... You could use the method above and make the final query a make table, then you'll have the structure you need.

 

[seat172]

I wish i could re design the table but it's part of our corporate system and has been developed over the years as a plaster for every sore. It has 5 million records and hundreds of columns so is too tightly interwoven with everything else for that option.

Regards

 

[JamesMcS]

Hmmm, OK then - I've had a similar problem before and ended up just making a separate table to get the job done. I think to get what you need you're going to need a VBA loop of some sort, cycling through the fields and appending to another table where it finds a field called "SupplierX".