query

[Guest]

I have a query as follows:
Day Product Code
2/1/05 SOS
2/1/05 SBT
2/1/05 SBL
2/2/05 SBM
2/2/05 SOS
.... ...
So, it's several products per day - max 5.
I'd like to get it as follows:
Day P1 P2 P3 P4 P5
2/1/05 SOS SBT SBL
2/2/05 SBM SOS
....

Please, advise how I could do it.

Thanks

 

[David S via AccessMonster.com]

I have a query as follows:
Day Product Code
2/1/05 SOS
2/1/05 SBT
2/1/05 SBL
2/2/05 SBM
2/2/05 SOS
... ...
So, it's several products per day - max 5.
I'd like to get it as follows:
Day P1 P2 P3 P4 P5
2/1/05 SOS SBT SBL
2/2/05 SBM SOS
...

Please, advise how I could do it.

Thanks

Use a Crosstab query - there's a wizard you can use from Query Designer to
get you what you want.

 

[Duane Hookom]

A crosstab query expects a value as the Column Heading. Alex didn't provide
one in the sample data. A column heading could be created with the
ultra-slow DCount()

ColHead:"P" & DCount("[Product Code]","[Query as follows]","[Product
Code]='" & [Product Code] & "' AND [Day]<=#" & [Day] & "#")