MULTI COLUMN REPORT: How To-Multiple headings: mon, tue, wed.

[sisieko]

i have database that allows a user to reserve a room for certain days throughout the week.
The table RoomRequest contains the following data:
DateReserved
RoomReserved
TimeStart
TimeEnd
Program

The form for entering data works correctly, I cannot seem to figure out how to create a report to display this information as intended.

The way i want it, is to have a column where RoomReserved is column1, then mon,tue,wed, .. in each column. i.e. The Reserved time for the day is listed for each Room. And i'll like to sort weekly. A weekly report.

This is how it should look

RoomReserved Monda y Tuesday Wednesday
Classroom1 8am-6pm 11am -2pm 9am - 2pm
Classroom2 4pm - 10 3pm -12noon 8am - 5pm
and so on
My question is, How can this layout be achieved in MsAccess considering my data table structure?
______________________
I have tried using a crosstab query: It only allows for one column. Moreso, since i want timestart-timeend and program to display under each column, i made a calculated field and choose it as "value" but that doesn't work for me... error pops saying i have to declare an "aggregate function" under value.

PLEASE TELL ME how to achieve this in access.

 

[gol4]

What you are asking can be done in a crosstab using max as the value if you have the fields set up properly.

Here is another thing to consider. This has some excellent examples that dhookem has to offer

http://www.invisibleinc.com/download.cfm?filFilID=6

 

[sisieko]

Max doesn't work.

I get error: "datatype mismatch in criteria expression".

I also checked out the link.. where dhookhom used sum.. for value in his crosstab query, it doesn't work for me either. Moreso, thats not what i want even though i tried using it.


Here's what my crosstab looks like:

TRANSFORM Max(Format([Start_Time],"Medium Time") & " - " & Format([End_Time],"Medium Time") & " " & [Program]) AS StartEndProgram
SELECT RequestTable.RoomReserved
FROM RequestTable
GROUP BY RequestTable.RoomReserved, RequestTable.StartDate, RequestTable.EndDate
PIVOT RequestTable.DateReserved In ("Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday");

I created another thread for this on the query forum, sorry for the redundancy.. im just trying to get help by any means possible.

 

[gol4]

This is the crosstab I was able to produce that looks like what I assume you want breaks it by week and room

TRANSFORM Min([starttime]) & " - " & Max([endtime]) AS booktimes
SELECT DatePart("ww",[StartDate]) AS weeknum, RequestTable.RoomReserved
FROM RequestTable
GROUP BY DatePart("ww",startDate]),RequestTable.RoomReserved
PIVOT Format([startDate],"ddd") In ("sun","mon","tue","wed","thu","fri","sat");

is it close??

 

[sisieko]

Holy Jesus! It works!!!

TRANSFORM Min([Start_Time]) & " - " & Max([End_Time]) & " " & Max([Program]) AS StartEnd
SELECT DatePart("ww",[DateReserved]) AS weeknum, RequestTable.RoomReserved
FROM RequestTable
GROUP BY DatePart("ww",[DateReserved]), RequestTable.RoomReserved
PIVOT Format([DateReserved],"dddd") In ("Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday");

It runs perfectly now gol4, i modified a little. God bless your heart. Thank you

 

[gol4]

Glad you are happy. I still suggest you consider reporting it out as in dhookoms examples. I'm sure you will discover the problem with this is if a room is booked from 8am till 9 am and again from 4p till 6p the query will make it appear as if it is booked from 8a till 6p even though there are 7 hours when it is open.

 

[sisieko]

you read my mind. I just noticed that, and was just gonna ask.

I will look at dhookoms sample thoroughly by tonight, i will let you how it goes.

I am just happy at least something worked .
making progress u know. I am getting closer by the minute

Thanks again.