I have the following data (sample) in a query. The PID and CID columns are ID numbers used to connect the info to other queries/tables.
PName--PID-----CName---CID
Joe------567 ------Jane-----241
Jim------888------Jill--------765
LLC------234------Jeff-------231
LLC------234------Bob------467
LLC------234------Sally------551
I am needing it to show the CName/CID in it's own column for each PName/PID
PName--PID -----CName1----CID1----CName2---CID2 etc with an unknown number of potential CName/CID columns.
Joe------567 ------Jane-----241
Jim------888------Jill--------765
LLC------234------Jeff-------231---------Bob------467-------Sally------551
The reason for this is I am trying to eventually merge data to create a letter addressed to Vendors that lists the PName and PID and all the related CNames/CIDs to that PNAME in the same letter.
I tried crosstab but I couldn't figure out how to dynamically create the C columns. I looked at the Transform/Pivot as well using MAX as aggregate function but again I don't now how to get it to create/name the columns.
Any advice would be appreciated. Thanks!
PName--PID-----CName---CID
Joe------567 ------Jane-----241
Jim------888------Jill--------765
LLC------234------Jeff-------231
LLC------234------Bob------467
LLC------234------Sally------551
I am needing it to show the CName/CID in it's own column for each PName/PID
PName--PID -----CName1----CID1----CName2---CID2 etc with an unknown number of potential CName/CID columns.
Joe------567 ------Jane-----241
Jim------888------Jill--------765
LLC------234------Jeff-------231---------Bob------467-------Sally------551
The reason for this is I am trying to eventually merge data to create a letter addressed to Vendors that lists the PName and PID and all the related CNames/CIDs to that PNAME in the same letter.
I tried crosstab but I couldn't figure out how to dynamically create the C columns. I looked at the Transform/Pivot as well using MAX as aggregate function but again I don't now how to get it to create/name the columns.
Any advice would be appreciated. Thanks!