Transpose the data

[deepakes2]

How Can I transpose the Following:




Can you help me write a query in Access to transpose the data


Table : Transpose
ID score course
1 3 M
1 4 C
1 T
1 4 E
1 4 M
1 5 C
1 2 T
2 M
2 3 T
2 5 E
2 11 R
3 3 M
3 4 C
3 6 R
desired output


ID M C T E R ..
1 3 4 - 4 -
1 4 5 2 - -
2 - - 3 5 11
3 3 4 - - 6




Thank you

[John_G]

You could use a crosstab query, but your data is not consistent. You could not have two rows for ID = 1 in the result.

The 2 rows with no result code should not be there (they are the same as not taking the course at all).

[deepakes2]

Crosstab Query and Pivot returning only one row.

In this case only one row associated with ID:1

There are multiple records in the table like ID:1 this which i need to display in output table in multiple rows.

The ID:1 taken courses multiple times . In the output I have to show all the data that is in the raw input table.
The ID:2 Taken Courses and i have to display what ever data is available in the input table to output table.
I hope you understand .

Thanks -

[John_G]

A crosstab query will not give you the results you need, not without additional information in the initial table. If you had another field in that table to indicate the "grouping", then it could.

Your data would have to look like this (showing only ID = 1):

ID group score course
1 1 3 M
1 1 4 C
1 1 4 E
1 2 4 M
1 2 5 C
1 2 2 T

ID's 2 and 3 would still need to show a group number, even if there is only one.

[deepakes2]

I am not Inclined towards using crosstab query or pivot tables.
I know what you are talking . But Sad that I dont have a criteria to group the ID's or any fields in the table.
I am sure this can be solved by writing a query or something .

Thank you .

[John_G]

I am sure this can be solved by writing a query or something .

If you want to make query columns which correspond to data vales (which is what you are wanting to do), then you use a crosstab query, that's what they're for. There is no way (that I know of) to write a Select query to do it.

If you have no data that can group the data they way you need to have it grouped, then a crosstab won't work either.

YOU know what the sequence of values in your table means, i.e. that (for ID = 1), when course = "M" repeats, it is the start of a new "line", but Access doesn't know that, and can't know it unless you tell it, which is what a group number field would do.

Your only other option I think would be to write a VBA routine to parse your initial table, and populate a results table using the required logic. Even then you might encounter problems with the order the initial records are read.

[deepakes2]

Please suggest me any solution you may have for me . I can add new field to the table but i have no idea how I can group the data. Please can you walk me through what ever u have for me. I can use any solution as long as I get the output.

I dont have the sequence of the values in this case.

Any VBA solutions are also welcome.

Thank you for ur time.



Thanks -