need help with a subquery

[myspiral]

I have a table

Type Age Number
Blue 1 3
Blue 2 4
Blue 3 5
Red 1 7
Red 3 6

I need a query that will output like this:
Type 1 2 3
Blue 3 4 5
Red 7 0 6


I'm thinking of using subqueries
ie SELECT table1.Type, (SELECT number FROM table1 WHERE [Age] = 1 AND [Type] ="Blue") AS 1
FROM table1
GROUP BY Type;

this works but I need "Blue" to be dynamic.
any help is appreciated.

 

[cranebill]

What if you created a form with a combo box, and Blue/Red could be options in the combo box. Use it as criteria in your query.

 

[PHV]

Have you tried to follow the CrossTab query wizard ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886