query to normalize data

[exo]

I have 2 tables.

The first is setup like this:

ID, NUM, VALUE
1 5 Dan
1 11 11/01/1985
1 65 Length
1 32 86
2 5 Dave
2 11 6/15/1972
2 65 Length
2 33 44
3 5 Greg
3 11 9/041961
3 15 53
3 8 M

And then a second table that is a key table for this NUM field.
NUM, VAL_Desc
5 - Name
8 - Gender
11 - Birthday
15 - Age
32 - Height
33 - Width
65 - Distance

So table two describes the value in table 1.

I am trying to find a way to move all records for ID 1 to one line, all records for ID 2 to the next line, etc..

Now, my real table two has like 60+ described values in it. And not every record uses all 60. So to do this I am thinking I need to have a query that assigns VALUE to a field that is labeled with the descriptor field in table 2.

I tried doing a standard select query and putting something like:
table2.NUM = 5, with the hope it would only out the value in that field when the NUM was 5 (so in this case, the Name). That obviously didn't work.

So then I started trying to look into a union query, but I couldn't figure out how to define the proper criteria to match the NUM values across tables.

Assistance would be greatly appreciates!

 

[plog]

I think a crosstab query will do the trick:

TRANSFORM Max(Table1.VALUE) AS MaxOfVALUE
SELECT Table1.ID
FROM Table1 INNER JOIN Table2 ON Table1.NUM = Table2.NUM
GROUP BY Table1.ID
PIVOT Table2.VAL_Desc;