Sql, Transform/pivot

[nullgumby]

Working in Access 2003.

I'm trying to write a query and have been looking at the TRANSFORM and PIVOT keywords, but can't seem to get them connected right.

My table is like this:

PrimaryKeyID    MyType    MyValue
1        Fruit    Apple
1        Veggie    Tomato
1        Meat    Chicken
2        Fruit    Orange
2        Veggie    Potato    
3        Fruit    Banana

I want the results to look like this:

PrimaryKeyID    Fruit    Veggie    Meat
1        Apple    Tomato    Chicken
2        Orange    Potato    NULL
3        Banana    NULL    NULL

Can anyone help me out with this? Thanks.

[NeoPa]

I suggest cheating.
Do it as a preliminary attempt by using the wizard to help, then see the resulting SQL and see how it does what you want.
Very important technique.
I learnt most of my VBA by getting Excel to 'Record' VBA macros for me then doctoring the results.

[nullgumby]

I suggest cheating.

That's what I did! :D ...this seems to work:

TRANSFORM First(MyValue) AS FirstMyValue
SELECT PrimaryKeyID
FROM MyTable
GROUP BY PrimaryKeyID
PIVOT MyType;