Crosstab queries

[hturgut]

I am novice to MS Access. I have created small database. I've got SELECT Query
StudentID Name Last Name CourseName StartTime FinishTime TeacherID RoomID
1 XX YY ZZZ 10:00 11:00 5 RoomA
2 AA BB TTT 09:30 10:30 6 RoomB

I want to display this data as follows
StartTime FinishTime -----------RoomA-------------- RoomB
10:00 ---------- 11:00 ----------- XX YY
-------------------------------------5 , ZZZ
09:30 ---------- 10:30 --------------------------------- AA BB
------------------------------------------------------------6, TTT

I tried to make crosstab query to achieve this but I couldn't.:banghead: Could you please help me on this. Thanks in advance

 

[vbaInet]

What you've just shown us isn't easily understandadble especially because of the 5 and 6 on the second line with no TeacherID header. Can you represent both tables in a nicer format, an excel file will be much better.

 

[hturgut]

I attached Excel File.

Basically, I am trying to display data in Matrix Format.

Thanks

 

[vbaInet]

Looks like a timetable to me.

First of all you need to concatenate the three fields into one in your query:

[COLOR=Red]AliasName[/COLOR]: First_Name & " " & Last_Name & Chr(10) & PrivateCourse_Name & ", " & Teacher_ID

Now use this field as the Value part of your Crosstab. To ensure that it shows, select First in the Total row for this field.

 

[hturgut]

Thanks a lot

It worked. But I did Crosstab query with this code. But it didn't work.

PARAMETERS [Forms]![frmTimetable]![sbfrmTimetable].[Form]![txtDate] Text ( 255 );
TRANSFORM First(qryTimetable_2.Booking) AS FirstOfBooking
SELECT qryTimetable_2.[Booking Start Time], qryTimetable_2.[Booking Finish Time]
FROM qryTimetable_2
GROUP BY qryTimetable_2.[Booking Start Time], qryTimetable_2.[Booking Finish Time]
PIVOT qryTimetable_2.Room_ID;

 

[vbaInet]

It didn't work meaning?? It throws an error or it doesn't give you the expected results or what?

 

[hturgut]

I attached error msg. I really don't get crosstab queries.

 

[vbaInet]

Upload your db let me see what's going on. Also indicate which query I should be looking at.

 

[hturgut]

Thanks a lot.

Actually I am trying to display timetable data in a matrix format in the form and able to make changes (teacher, room, day, time) in the form.

You need to open frmTimetable then qryTimetable and qryTimetable_2

 

[vbaInet]

Look at qryTimetable_2.

By the way I removed the reference to your form, i.e. [Forms]![frmTimetable]![sbfrmTimetable].[Form]![txtDate]

 

[hturgut]

Thanks a lot . I really appreciate for your help. Thanks

 

[vbaInet]

No problem!