Merging/Folding Records

[Dennis]

I have a MS Access table with three columns, DATE, CLASS, LESSON

01/01/2006 CLASS1 Fishing
01/01/2006 CLASS2 Baking
01/01/2006 CLASS3 Movies
02/01/2006 CLASS1 Movies
02/01/2006 CLASS2 Hunting
03/01/2006 CLASS1 Weeding
03/01/2006 CLASS2 Pruning
03/01/2006 CLASS 3 Typing

I would like to code a query that will help me generate a output that
has a table with the data, and the class1, class2, and class3
descriptions. Something like

Date CLASS1 CLASS2 CLASS3
01/01/2006 FISHING Baking Movies
02/01/2006 Movies Hunting
03/01/2006 Weeding Pruning Typing

Sometimes there won't be a class entry for some days.

Any suggestions on how to buld the table?

[Dennis]

I figured it out using a PIVOT and TRANSFORM to get what I need.
Unfortunately some of the column names that get generated have spaces
in them. Any thoughts as how to get rid of that? I don't think I can
use the "AS" in my select as those fields aren't there until the PIVOT
is done.

<cfquery name="Get_Events" Datasource="Calendar">
TRANSFORM min(calendar.lesson) as minlesson
SELECT StartDate
FROM Calendar
WHERE (StartDate BETWEEN #CreateODBCDate(startrange)# AND
#CreateODBCDate(endrange)#)
group by calendar.StartDate
PIVOT calendar.class;
</cfquery>