Transpose rows in columns (vice versa)

[wtrimble]

I have report based off of a query. The report is coming out as shown:

Sample ID Type SiO2 AL2O3 FE203 ....
01 Bulk 22 .12 100
01 Surface 18 .11 89
02 Bulk 21 .25 85
02 Surface 12 .45 77

I need the report to look like this:

Sample ID:01 Sample ID: 02
Bulk Surface Bulk Surface
Si02 22 18 21 12
AL203 .12 .11 .25 .45
FE203 100 89 85 77

Is this possible?? I read some places online about crosstab and union queries but didn't seem to make sense. Thanks for any help

 

[wtrimble]

sorry tables didn't come out so well after typing in.....just starting out with these blogs...

 

[MSAccessRookie]

In the Quick Format Icons above the window where you enter Text, you will find one that looks like "#". Click on that any you will get two format tags, each surrounded by "[" and "]". The first one ("Code") declares the beginning of a formatted section. The second one ("/Code") declares the beginning of a formatted section. Try inserting your tables (formatted as you want them) between them and it should produce what you want. You may also find that you will need to use a Fixed Font (like Courier New) as well.

 

[wtrimble]

Are you talking about within the report?? Confused

 

[Kryst51]

To get your table layout in your first post to appear correctly follow the steps posted by MSAccessRookie

 

[wtrimble]

Right, but can you be more specific. Where am I making these changes in?: in the table, query, report...? Looked in all of them but can't find what you're talking about

 

[Kryst51]

You are misunderstanding, he is trying to help you fix your Original Post so that it is readable, not trying to answer your question.

 

[MSAccessRookie]

Right, but can you be more specific. Where am I making these changes in?: in the table, query, report...? Looked in all of them but can't find what you're talking about

NONE OF THE ABOVE.

You will need to use the Code Tags here in the Message board to make your table or code easier to read. I can post what I think you mean, but it is always better for you to show what you mean. My previous post was only menat as an explanation as to how to do this.

------------------ What I think you may have meant ------------------

[FONT=Courier New]I have report based off of a query. The report is coming out as shown:

[U]Sample ID[/U]   [U]Type[/U]      [U]SiO2[/U]   [U]AL2O3[/U]   [U]FE203[/U] ....
    01      Bulk       22     .12     100
    01      Surface    18     .11      89
    02      Bulk       21     .25      85 
    02      Surface    12     .45      77

I need the report to look like this:

        Sample ID:01     Sample ID: 02[/FONT]
[FONT=Courier New]        [U]Bulk [/U][U]Surface[/U]     [U]Bulk [/U][/FONT][FONT=Courier New][U]Surface
[/U]Si02     22    18         21    12
AL203   .12   .11        .25   .45 
FE203   100    89         85    77
[/FONT]

 

[wtrimble]

Here it is:

          [U]Sample ID[/U]        [U]Type[/U]    [U]SiO2[/U]   [U]AL2O3[/U]  [U]FE203[/U] ....
              01          Bulk      22    .12      100
              01        Surface     18     .11     89  
              02           Bulk     21     .25     85 
              02         Surface    12     .45     77

I need the report to look like this:

                Sample ID:01        Sample ID: 02
            [U]Bulk[/U]    [U]Surface[/U]          [U]Bulk[/U]   [U]Surface[/U]
Si02         22       18              21      12
AL203       .12       .11            .25     .45 
FE203        100       89             85      77

I need the rows and columns switched more or less.
Thanks Kryst51

 

[wtrimble]

Any Thoughts?

 

[Kryst51]

Thanks Kryst51

Your Welcome

 

[Kryst51]

I don't know how to achieve what you want, but here is a link to some tips for crosstab queries which might help you flesh out what you need to do.

Allen Browne's crosstab query techniques

 

[Poppa Smurf]

Have a look at this link http://support.microsoft.com/kb/283875/en-us

Before you start you may need to join your sample ID and Type to give you the desired heading, otherwise they will be separate columns.

 

[wtrimble]

Thanks guys

 

[Poppa Smurf]

Further to my earlier post regarding the link to the knowledgebase site. I found problems with the solution in that it caused the database to "bloat" due to a lot of Read and Write processing. With 50 rows and 60 columns this was time consuming etc.

To overcome this problem coding was developed to read the input table into an array then manipulate the array to write new each record to the output table.

I have attached a sample database with two modules mod_transpose_demo using data R01 and Col_1 with the value C1 so that you can see the result in Col and row format.

The second module mod_transpose is the transpose solution to the original question.

Just a couple of notes
For the input the maximum rows is 255 and maximum columns is 255.

The output data type must be one type, you can not have a mixture of text or number etc.