Rows -> Columns CrossTab, PivotTable??

[chiuchimuN]

I've been doing this by VBA code filling in an unbound Flexgrid by textarray(i) but its slow and there must be a better way. Every time I find an SQL technique I notice its always faster and simpler than VBA coding. So, I hope someone knows something out there.

Original Table data
ID Name DateAttend
1 John 5/16/02
2 Marry 5/16/02
1 john 5/15/02
2 Mary 5/15/02
.....
3 David 4/13/02
1 Mary 4/13/02
.....
4 Steve 3/02/02
.....

The resulting table or query needed
ID Name March April May ...
1 John 3/11/02 4/04/02 5/15/02
3/17/02 4/11/02
4/22/02
2 Mary 3/1/02 4/13/02 5/15/02
5/16/02
4 Steve 3/02/02 0 0
....

The months span the last 12 months from current date

 

[pendragons10]

Good explanation of crosstab SQL using TRANSFORM statement at

http://www.cse.bris.ac.uk/~ccmjs/acc97-r5.htm#ex16