Exporting Query to Excel/Transposing Spreadsheet

[mulch17]

Hello everyone,

I'm wondering if someone could give advice on a problem I'm having, to see if I'm going completely in the wrong direction before I start with this.

I have a query that runs in Access that I would like to export to Excel. My exporting code works fine, but I would like to change the visual layout of the spreadsheet.

I think it will be much easier just to show this visually. Currently my spreadsheet structure is like this when it's exported:

ID  |  Comments
1   |  A
1   |  B
2   |  C
3   |  D
3   |  E
3   |  F
3   |  G
4   |  H
5   |  I
5   |  J
.....

Is there a way to make it grouped like this:

ID  |  Comment 1  |  Comment 2  |  Comment 3  |  Comment 4  |  .
1   |  A          |  B          |             |             |  .
2   |  C          |             |             |             |  .
3   |  D          |  E          |  F          |  G          |  .
4   |  H          |             |             |             |  .
5   |  I          |  J          |             |             |  .
.....

The challenge is that I don't know a priori how many rows or columns there will be. Those numbers are determined programatically, based on user input.

My only idea as of now is to do a series of nested loops and if statements, but I'm still trying to picture them all in my mind. I'm sure I can eventually figure this out, but is there another way that's easier? It's kind of like a transpose, but not completely.

Thanks for reading!

 

[plog]

This is a simple pivot table (https://support.office.com/en-AU/Ar...eet-data-a9a84538-bfe9-40a9-a8e9-f99134456576) if you can determine which records are what numbered comments. Your data needs to look like this:

ID, Comment, CommentNum
1, A, 1
1, B, 2
2, C, 1
3, D, 1
3, E, 2
3, F, 3
3, G, 4
...

Get it to look like that and its a simple pivot table.

 

[mulch17]

This is a simple pivot table if you can determine which records are what numbered comments. Your data needs to look like this:

ID, Comment, CommentNum
1, A, 1
1, B, 2
2, C, 1
3, D, 1
3, E, 2
3, F, 3
3, G, 4
...

Get it to look like that and its a simple pivot table.

Thanks for your quick reply. This looks similar to what I want, but I don't see how to just keep the values. The pivot table options keep displaying aggregate function values in the table (sum, count, etc). I don't want to display any counts or sums, just the values themselves.

 

[plog]

You're right, I apologize. A Pivot table is not a good way to do this. I believe a Cross tab query would be better. You still need it in the same format I mentioned (3 columns with CommentNum). Then you can do a crosstab using CommentNum as Column headings, ID as row headings, Comment as Value and then select the Max of that value in the GROUP BY.

 

[JHB]

Run the below query and see if it full feed your requirement, (remember to change the table name "IDAndComments" to yours).

TRANSFORM Max(Comments) AS MaxOfComments
SELECT ID
FROM IDAndComments
GROUP BY ID
PIVOT "Comment " & Format(Asc(UCase([Comments]))-64,"00");

 

[mulch17]

Sorry for bringing back an old thread, I got pulled into other work and didn't revisit this until today.

This took care of exactly what I needed. The only problem was that the comment fields were actually Memo/Long Text, and I kept getting errors using the Max function. For any future readers out there, I used First(Comments) instead, and that seemed to do the trick.

I now have one other question, but I will put it into a separate thread, since you guys already answered my question for this thread. Thanks for your replies!!!!!