Split query results into 5 fields in a report

[cindy007]

Hi,
I have a query, which gives me the weekday number for each day of the year, excluding weekends ...Monday to friday, using

SELECT [Table3].[TheDate], [Table3].[Message], DatePart("w",[TheDate]) AS Expr1
FROM Table3
WHERE (((DatePart("w",[TheDate])) Between 2 And 6));

Is there any way of having all day 2's in 1 column all day 3's in 1 column etc...in the same report, at the moment, I am only able to get all the day's together in one column, what method would I use to seperate or group the day types together in their own column or field?

TheDate Day TheDate Day TheDate Day

06 Jan 2003 2 07Jan 2003 3 08 Jan 2003 4 .....
13 Jan 2003 2 14 Jan 2003 3 15 Jan 2003 4
20 Jan 2003 2 21 Jan 2003 3 etc...etc

Instead of what I am getting now ...
TheDay Day
01 Jan 2003 4
02 Jan 2003 5
03 Jan 2003 6
06 Jan 2003 2
07 Jan 2003 3
etc...etc..
Cindy

 

[Rich]

Use a crosstab query

 

[cindy007]

Re- Crosstab query

Rich,
Thanx for that suggestion, I have never used a CrossTab query before...I'll give it a go !

Cindy

 

[kflick]

use columns

as long as you don't miss any days of the week you could set up 6 cols on your report and go across then down

that should give you the appearance you want