Crosstab queries (1 Viewer)

H

hturgut

Registered User.

Local time

Today, 12:12

Joined

Dec 18, 2010

Messages

24

• Aug 28, 2012

• #1

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

 

Last edited: Aug 28, 2012

V

vbaInet

AWF VIP

Local time

Today, 03:12

Joined

Jan 22, 2010

Messages

26,374

• Aug 28, 2012

• #2

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.

 

H

hturgut

Registered User.

Local time

Today, 12:12

Joined

Dec 18, 2010

Messages

24

• Aug 29, 2012

• #3

I attached Excel File.

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

Thanks

 

Attachments

• CrossTab.xls

  27.5 KB · Views: 155

V

vbaInet

AWF VIP

Local time

Today, 03:12

Joined

Jan 22, 2010

Messages

26,374

• Aug 29, 2012

• #4

Looks like a timetable to me.

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

Code:Copy to clipboard

[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.

 

H

hturgut

Registered User.

Local time

Today, 12:12

Joined

Dec 18, 2010

Messages

24

• Aug 29, 2012

• #5

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;

 

V

vbaInet

AWF VIP

Local time

Today, 03:12

Joined

Jan 22, 2010

Messages

26,374

• Aug 29, 2012

• #6

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

 

H

hturgut

Registered User.

Local time

Today, 12:12

Joined

Dec 18, 2010

Messages

24

• Aug 29, 2012

• #7

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

 

Attachments

• Crosstab_Error.pdf

  98.5 KB · Views: 113

V

vbaInet

AWF VIP

Local time

Today, 03:12

Joined

Jan 22, 2010

Messages

26,374

• Aug 29, 2012

• #8

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

 

H

hturgut

Registered User.

Local time

Today, 12:12

Joined

Dec 18, 2010

Messages

24

• Aug 29, 2012

• #9

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

 

Attachments

• Prestige Education_2012.zip

  1.6 MB · Views: 73

V

vbaInet

AWF VIP

Local time

Today, 03:12

Joined

Jan 22, 2010

Messages

26,374

• Aug 29, 2012

• #10

Look at qryTimetable_2.

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

 

Attachments

• Prestige Education_2012.zip

  1.6 MB · Views: 80

H

hturgut

Registered User.

Local time

Today, 12:12

Joined

Dec 18, 2010

Messages

24

• Aug 29, 2012

• #11

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

 

V

vbaInet

AWF VIP

Local time

Today, 03:12

Joined

Jan 22, 2010

Messages

26,374

• Aug 29, 2012

• #12

No problem!