Converting Rows to Columns VBA Access

[derekben]

Hello everybody,


I have been struggling with this for awhile now. I am trying to convert a table that looks like the following...

Customer Name SumofSum of Bill Rate Reviewer
000462 John 500 Mike
000224 Mike 900 Jeff


I would like to covert it to....

Customer 000462 000224
Name John Bill
SumOfSum.. 500 900
Reviewer Mike Jeff

I don't think Access has any functions for this but I am not 100% sure. I know this can be done in a Module but I am not sure of how to code it. Any help is much appreciated. Thanks.

 

[billmeye]

Are you saying you want one long row for each Column name?

For example, the current table has 20 records so you want all 20 Customers to be on a single line and then 20 Names on another line etc.? If so, your new table would have only 2 columns, the first column is the name of the column from the original table and the 2nd column is a long string of all the data from the first table?

 

[derekben]

No, in your example we would have 21 columns.

Customer Name Bill Rate Reviewer
461 John 250 Carol
462 Mike 300 Bill
463 Jeff 450 Bob
464 Bill 100 Ben
465 Carl 235 Larry
466 Dan 90 Derek
467 Josh 100 Jon
468 Kobe 540 Billy
469 Lebron 600 Barry



Customer 461 462 463 464 465 466 467 468 469
Name John Mike Jeff Bill Carl Dan Josh Kobe Lebron
Bill Rate 250 300 450 100 235 90 100 540 600
Reviewer Carol Bill Bob Ben Larry Derek Jon Billy Barry

 

[billmeye]

Have you worked with recordsets in VBA? To do what you would like you would need to parse through your recordset and assign the data to the new tables fields. If you have a DB you want to attach and I can set it up for you.

 

[derekben]

Here you go. Thanks View attachment Performance Assessment.zip

 

[billmeye]

Hi Derek. The attached file has a function called TransCol that is in Module1. To run the function you can either go to the immediate window and run it from there or attach it to a button on a form. Let me know if you have any problems.

 

[derekben]

Thanks Bill. It seems like you did what I was looking for but I am not familiar with modules. I would like to create a button on a form with the module. How would I go about that?


Thanks!!

 

[billmeye]

Derek, I added form1 with the button and controls you will need to run the function. Let me know if that works for you.

 

[derekben]

Thanks Bill!!! Did not know you could do that. I really appreciate it. I am going to leave this thread open for a couple more days because I have some questions.


Thanks again.

 

[billmeye]

Where else can a few lines of text be so much fun.

 

[derekben]

Haha thanks again.

I am actually working on it now and what you created got me almost there. Now the new table will show the following when transposed:
ColName ColData
Name Adam - Adam - Adam - Adam - Jon - Jon
Customer xxx - yyy - zzz - aaa - lll - bbb
Amount
Reviewer

Is there any way to split the names into different columns so it would be like this
ColName Col1 Col2 Col3 Col4 Col5 Col6
Name Adam Adam Adam Adam Jon Jon
Customer xxx yyy zzz aaa lll bbb
Amount
Reviewer

 

[billmeye]

Hi Derek. The second button will generate a new table with each data broken out into it's own column. I'm sure there is a limit to how many columns you can have in a table (it might be 255) and for each row in the original table you will create a new column in the new table so any table with more than 255 rows will probably generate an error.

 

[derekben]

Billmeye, thanks again. I can see you have a good understand of access. I am impressed.

Ofcourse I have another issue, not sure if its possible but thanks.

If you check out the assigning the reviewer form. I have added a subform of the table you created, which was exactly what I was looking for. Now, I want the subform to only show the columns of the Names that are in the combo box(combo11) that displays the name. I am not sure if this is even possible but yeah thanks again. You really have been an amazing help.

I have attached the updated database.

 

[billmeye]

Not quite sure what you are actually trying to accomplish but here is what I think you are looking for. In the end I'm confused by what your end goal is with all these criss-crossed tables.

 

[derekben]

That is what I am looking for. I wanted the subform to show the inputter how many customers they have. It is kind of hard to explain but this is what I am looking for. I do appreciate you time a lot Bill. Thank you!

 

[billmeye]

Your welcome. Good luck with the rest of your project.

 

[derekben]

What did you change in the database? I am looking at it now and can not find what you changed. I understand that the subform is only showing the name related to the field, but where did you edit this field to do that.

 

[billmeye]

The code is in the main form, not subform.