Crosstab reports

[AndyBannister]

In A2K I have a database which holds timetable of courses. Fields consist of 'date', 'tutor', 'room' and course title. What I want to try and do is subgroup by 'date', but then have rooms stretching out horizontally across my page, with the other details below.

e.g.
DATE Room 1 Room 2 Room 33
1 Oct event event event
2 oct event event event

Is this actually possible --- reports are not my strong point, but it seems very, very hard --- would a crosstab query do the job? I seem to see recall seeing one in the MS Solutions database some years back, but that won't open properly in my version of A2K.

Any advice greatly appreciated.

Andy Bannister

 

[AndyBannister]

Thanks for your advice Pat, but the problem is that crosstab queries seem to only be happy to summarise numeric data . Whereas I want text to appear

e.g. in the example I gave, where say 18/08/01 (date) and "HB1" (room) intersect, I don't want a subtotal, but rather the contents of the "event" field (which is a textual one).

This was why I was wondering whether crosstabs could perform such a task ...

Any further advice would be appreciated.

Many thanks.

 

[Rich]

I use crosstab queries to output text, build them manually without the wizard.

 

[AndyBannister]

Rich,

Thanks --- that's exactly what I want to do. Perhaps I'm just being deliberately slow on the update --- but all the options the crosstab query seems to present me with are numeric ones (e.g. VALUE) --- how does one get a single text value to appear?

Thanks for the reply,

 

[Rich]

If you can understand this you should get an idea
TRANSFORM First([Description] & " " & [Number] & " " & [Address]) AS Det
SELECT AppDte.AppointmentDate
FROM [Properties owned] INNER JOIN (AppDte RIGHT JOIN Appointments ON AppDte.AppointmentDate = Appointments.AppointmentDate) ON [Properties owned].AddressID = Appointments.AddressID
WHERE (((AppDte.AppointmentDate)>#1/1/90#))
GROUP BY AppDte.AppointmentDate
ORDER BY AppDte.AppointmentDate DESC
PIVOT Appointments.AppointmentType In ("AB","AM","ALL DAY","8AM","9AM","10AM","11AM","12PM","PM","1PM","2PM","3PM","4PM","5PM","6PM");
HTH