Data format Transform

[mr moe]

Hi I have a table in access with this format
ID Grade
1 A
5 D
6 B
1 B
5 C

need to write a query that produce the output like the format below
ID Grade1 Grade2
1 A B
5 D C
6 b

can someone please help, basically looks like the opposite of transform right?

 

[plog]

This can be done with a sub-query to assign order to your data and then a cross-tab to format it like you want.

However, you haven't provided enough information to do this. You need a field so that a computer knows how to order your data. Do you have a field that you can apply order to? E.g. a date field, an autonymber primary key? You need a way so that a computer can know that 1 A falls into the Grade1 bucket and 1 B falls into the Grade2 bucket.

 

[isladogs]

I've sort of done it with just a crosstab query.

It's not perfect but perhaps its good enough?
As plog said, you need a PK field as well.

Alternatively you could concatenate the values with a space as separator