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;