help needed please? Restructuring access table data

[towser]

Hi.
I have a lot of data in a table and I want to make a query to restructure the data as follows.
I have shown an example below of what I want to achieve.
I want to restructure the data in table 1 to get it in a new table in the structure shown in table 2.

[B]Example[/B]


Table 1


[B]ID    label   value[/B]
----------------------
1      type_a     25
1      type_b     35
1      type_c     45
2      type_a     15
2      type_b     46
2      type_c     56


Table 2



[B]ID   type_a   type_b   type_c[/B]
---------------------------------
1        25       35        45
2        15       46        56

Thanks

 

[The_Doc_Man]

The easiest way to do this is a hand-built table, an append query, and two update queries, but it only works if you are flat-out guaranteed that one of those type-? codes is present for EVERY ID YOU NEED TO REPRESENT.

Like, if there ever is a case for type-a not being present, don't base the append query on it.

OK, so here is what you do.

Build a table with your desired fields. Empty table is OK. Define your fields to hold everything you need.

Now select one of the type codes you know is present for EVERY ID code. Base an Append query on that. Append the ID code and the value for the type-a code, leaving the other fields null.

Go back and select another type code. Update the records based on the ID code, filling in the type value for that type-code.

Do it again for the third type code. You are done.

Doing it this way, it is easier to see what you are doing at every step of the way. The confusion factor is less. The individual steps will be less likely to give you trouble.

A prime example of Julius Caesar's advice: Divide and conquer!

 

[ecniv]

Check the Access query 'Crosstab'. I think you have enough fields to run it... may be, otherwise its the method posted above