Pivot or CrossTab or Normalize or ? (1 Viewer)

[tanyamc]

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!

 

[Minty]

What about concatenating the related records?

Microsoft Access tips: Concatenate values from related records

Return a string containing the values of related records.

allenbrowne.com

 

[Uncle Gizmo]

In essence you've got two tables there, you need to divide your data up into two tables. You can use a recordset loop to loop through the first set of Records and then append the second set of Records.

'PID ------ PName
'567 ------ Joe
'888 ------ Jim
'234 ------ LLC

'PID ----- CName --- CID
'567 ----- Jane --- 241
'888 ----- Jill --- 765
'234 ----- Jeff --- 231
'234 ----- Bob --- 467
'234 ----- Sally --- 551

 

[Uncle Gizmo]

Actually, you've got three tables, you've got two sets of Records and then a junction table.


'tblPName
'PID ------ PName
'567 ------ Joe
'888 ------ Jim
'234 ------ LLC

'tblCName
'CID --- CName
'241 --- Jane
'765 --- Jill
'231 --- Jeff
'467 --- Bob
'551 --- Sally

'tblJunction
'PID ----- CID
'567 ----- 241
'888 ----- 765
'234 ----- 231
'234 ----- 467
'234 ----- 551

 

[Uncle Gizmo]

If you wanted to be really clever and make a neat solution you could actually put all the names into one table. Create a names table with a unique ID.

'tblNames
'ID --- CNameOrPName --- Name
'1 -----CName ------ Jane
'2 -----CName ------ Jill
'3 -----CName ------ Jeff
'4 -----CName ------ Bob
'5 -----CName ------ Sally
'6 -----PName ------ Joe
'7 -----PName ------ Jim
'8 -----PName ------ LLC

 

[Uncle Gizmo]

Then you'd have a new Junction table taking all of its data from the combined names table:-

'tblJunctionNEW
'PID ----- CID
'6 ----- 1
'7 ----- 2
'8 ----- 3
'8 ----- 4
'8 ----- 5

 

[tanyamc]

S

Actually, you've got three tables, you've got two sets of Records and then a junction table.


'tblPName
'PID ------ PName
'567 ------ Joe
'888 ------ Jim
'234 ------ LLC

'tblCName
'CID --- CName
'241 --- Jane
'765 --- Jill
'231 --- Jeff
'467 --- Bob
'551 --- Sally

'tblJunction
'PID ----- CID
'567 ----- 241
'888 ----- 765
'234 ----- 231
'234 ----- 467
'234 ----- 551

So if I go this route, how do I use this in the final document/report to call all the CNames for a PName before creating the next page/letter?

 

[Uncle Gizmo]

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

Please explain how you envisage placing Joe, Jim and LLC in the final document/report. I need to know what your final document/report looks like to be able to advise you.

 

[MajP]

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.

That format does not even seem very readable. Why not just simply Group your report? Seems more readable. Maybe I misunderstand your desire.

PName: Joe PID: 567
-----CName---CID
------Jane-----241

PName: LLC PID: 234
-----CName---CID
------Jeff-------231
------Bob------467
------Sally------551

 

[tanyamc]

Please explain how you envisage placing Joe, Jim and LLC in the final document/report. I need to know what your final document/report looks like to be able to advise you.

I am creating
1) a report that is basically a letter that will merge all the info I am extracting from various reports. For each PName there will be a letter, and in that letter the CNames associated with that PName will have to be included.

Vendor Name​

VAddress​

VCSZ​

​

Dear Vendor​

​

You have purchased the following commodities from LLC.​

​

The following people are associated with LLC for purchases.​

Jim​

Joe​

2) a report that list PNames and Associated CNames by Vendor (I have another table that lists the PNames associated with each Vendor)


Again, I have no idea what the max number of CNames will be per PName (creating a tool for others to use to analyze their data so will vary by user).

Address and commodity information will also be added to the letter from other data sources. Ideally all this would come in a prefab report with columns needed to be mail merged into a Word document, but alas I have to connect 6 different reports to get all the info I need for the letters. And Word can't handle multiple rows per letter in a mail merge. So I am working in Access.

 

[tanyamc]

That format does not even seem very readable. Why not just simply Group your report? Seems more readable. Maybe I misunderstand your desire.

PName: Joe PID: 567
-----CName---CID
------Jane-----241

PName: LLC PID: 234
-----CName---CID
------Jeff-------231
------Bob------467
------Sally------551

I am automating completion of a mandatory letter that dictates format and content, which are now being done manually from printouts of the other reports, prone to error and a huge waste of time.

 

[MajP]

That format you show
LLC------234------Jeff-------231---------Bob------467-------Sally------551

Is that just display or is that a table/query with those columns? If the latter can you export to excel to meet your needs?

 

[Uncle Gizmo]

One more thing which I think should be clarified at this stage, you state "letter" and you also state "Report". When you say "Report" do you mean the MS Access object, a Report?

 

[MajP]

If what you show is a table with columns, I cannot think of a way to do it in a crosstab. I am not that versed in pivots, but also doubt it is doable. I could be done in a temp table, but not easily and pretty inefficient. If I had to create a table like that, I think I would read the recordset and write into excel columns or a CSV. Like Uncle Gizmo, I would really need to understand the final product.

 

[Uncle Gizmo]

Dear Vendor

You have purchased the following commodities from LLC.

How do you associate Vendor and LLC?
Are they fields in a Table?
Do you select LLC from a list in a combobox?

 

[Pat Hartman]

I don't use mail merge. It is too hard to control. I use OLE automation and to show a "list", I create a table and insert the table. I'm in the middle of something. If you need an example, I'll post it later this afternoon.

 

[tanyamc]

That format you show
LLC------234------Jeff-------231---------Bob------467-------Sally------551

Is that just display or is that a table/query with those columns? If the latter can you export to excel to meet your needs?

This is what I would need the table to display in order to use in Word for Mail Merge (rather than using a report in Access to mimic the letter).

 

[tanyamc]

One more thing which I think should be clarified at this stage, you state "letter" and you also state "Report". When you say "Report" do you mean the MS Access object, a Report?

If I can get the info in the proper format for Word Mail merge I would use that to complete the letters I am required to send.

If I can't and I have to use a Report in Access, it will be designed to mimic the letter format I am required to use.

 

[tanyamc]

If what you show is a table with columns, I cannot think of a way to do it in a crosstab. I am not that versed in pivots, but also doubt it is doable. I could be done in a temp table, but not easily and pretty inefficient. If I had to create a table like that, I think I would read the recordset and write into excel columns or a CSV. Like Uncle Gizmo, I would really need to understand the final product.

I replied to his post showing the output I need/letter content.

Pivot or CrossTab or Normalize or ?

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...

www.access-programmers.co.uk

 

[tanyamc]

How do you associate Vendor and LLC?
Are they fields in a Table?
Do you select LLC from a list in a combobox?

One of the lists we are working from has a column for each in the same row. LLC is a PName.

There would be no selections, just run a batch at a time (annually) to print letters that need mailed.