Cross Tab Query

[Nengster]

Hi, I would like to create a report that would allow me to display records based on the name of the course, the company particulars date of the course as well as the number of particpants of the course in question on a particular date. The date and name of the courses are not fixed at the beggining of the year and hence the dates and number of courses held each year may be different. I realised that I have to use a cross tab query to create such a report since the dates and number of courses held each year will keep changing. In addition, I would also like to generate these statistics for each different type of course. I was thinking of using a form to prompt the user on the name of the course in which he is intersted in generating the statistics for and then the cross tab query will generate the data to current date based on the name of the course. However, I am very confused about using the cross tab query and quite unsure of how I may achieve my intended report. Would someone kindly enlighten as to how I may proceed. Step by step instructions would be appreciated.


Regards,
Julian

N.B. Attached is an excel spreadsheet of how my report is intended to look like

 

[apr pillai]

I think you should organize your data in different Tables first.

Table-1: Name of the Course with a Unique Course Code
Table-2: If the Companies are permanent attendees of the Course then you can create a base table for Companies with Company Codes.
Table-3: will have the following fields:
1. Selected Course Code
2. Company Code
3. Date of Course
4. Number of attendees.

if the same Company selects the same course for different dates with different number of attendees then create a separate record for each date with Course Code and Company Code.

When you are ready to go for the Cross-Tab Join Table-1, Table-2 to Table-3 on Course Code and Company Code respectively, select Course Name, Company Name, Date Field as Column heading and attendees as Value to tabulate.