Converting Rows to Columns and Columns to Rows

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

[TG_W]

Have you tried a Crosstab query?

[derekben]

Yes. The issue with the crosstab table query is that each name does not have data for every customer. I do not want to show blank cells, because this go straight to a form. I only want to show the Name and only customers they have worked on.

It is difficult to explain, if you have any more questions or need more information please let me know. Thanks!!!

[TG_W]

If the information is going straight to a form, why not query the table as is filtering out the blank fields as necessary, then arrange the fields like you want to display them on the form?

[derekben]

We ran the crosstab query and have Customer, Name , SumofSum of Bill Rate, Reviewer as our headers but when it displays in the form, it shows up as rows instead of columns. What I am really trying to figure out is how to transpose rows into columns in the easiest possible way. Thanks.

[TG_W]

Can you share your database for further analysis? Just make a copy, remove all important data, add one line of example data, and upload then post.

[derekben]

It won't let me upload it here. Can I email it to you or is there another way to upload?


Thanks

[TG_W]

Try compacting and repairing the database then zipping it.

[derekben]

Performance Assessment.zip Here is the zip file. Thanks

[TG_W]

Thanks. Are you trying to get the information into a report for analysis or a form for further data entry?

EDIT

[derekben]

Form for further data entry.

[TG_W]

See if Form 2 gets you what you are looking for.

The problem with saving your data as you were noting in your original post is that you would have to create a new field for each customer. The way I set Form 2 up is it is not tied to your main data table, but it will write to it. The layout appears vertically using the 'Stacked' arrangement. I also went through the your main data table and removed the spaces from the field names. In Access, it is bad practice to have spaces in your titles for both field names and table names. I also changed 'Name' to 'TName' since Access uses 'Name' which could cause you problems in the future.

If it is about what you are looking for and you want to change the form name, make sure you change the form name call in the VBA I wrote in. The green font wording are notations I made throughout the code, in case you are not familiar with VBA.

[derekben]

Thanks!!! That's exactly what I was looking for.