query for attendance

I have an attendance database to record student atendance.

My database data like following:

studentID, Attendance date, Attendance code
1 Dec/01/2008 Absent
1 Dec/10 /2008 Sick
1 Nov/5/2008 Late


I need a query report like following:

Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thur Fri
Nov 1 2 3 4
5 6 7
WK WK X X
Late X X

Dec 1 2 3 4 5 6 7 8 9
10 11 12
A X X X X WK WK X X Sick
X X


It is a calendar like align weekday report.

If students attend to school then mark "X", but no data in the
database.

I can use 12 queries to union them in one query.

The only thing is I need to know which column to put the data, because
I can not assume same date on the same column.

Are there any way to get this result without temparory tables?

Your help is great appreciated,

Google concantenate fields in Access. I know there are some solutions out
there.

"inungh" wrote:

I have an attendance database to record student atendance.

My database data like following:

studentID, Attendance date, Attendance code
1 Dec/01/2008 Absent
1 Dec/10 /2008 Sick
1 Nov/5/2008 Late


I need a query report like following:

Mon Tue Wed Thu Fri Sat Sun Mon Tue Wed Thur Fri
Nov 1 2 3 4
5 6 7
WK WK X X
Late X X

Dec 1 2 3 4 5 6 7 8 9
10 11 12
A X X X X WK WK X X Sick
X X


It is a calendar like align weekday report.

If students attend to school then mark "X", but no data in the
database.

I can use 12 queries to union them in one query.

The only thing is I need to know which column to put the data, because
I can not assume same date on the same column.

Are there any way to get this result without temparory tables?

Your help is great appreciated,