Crosstab queries or report? (Access 97)

Topic

New Reply

I have a database that captures attendance for varies course that we teach. I need to produce a report that will take the data and align it in columns instead of the data in rows.

For Example:
(names of the courses)

Name…..Job Title…..Gen101….Doc101….Spec101….Spec102…total hrs

Deborah…trainer………YES……..NO……….YES………..YES……….6

My report currently has:
Deborah, Trainer
Gen101…yes
Doc101…no
Spec101..YES
Spec102…YES

I have a table of employees, courses information and courses offered. An employee is linked to the courses offered by SS# and the courses offered is linked to Course information by course code. I need to know what training thwy have had over a time period, whether they attended, have not registered and how many hours completed. I need to sort by department and then name.

I have played around with crosstab queries and I can get some of the information but not all. I also tried the multiple-column report but I could not get it to work. I don’t want the names under the course, I want each person who may have up to 8 entires to have one record with their name and whether or not they have been to the certain class. It doesn’t seem hard but I am having problems!!

HELP!!

Deborah

Reply | Quote

Replies

You can combine crosstab queries and totals queries for this.

1. A crosstab query to return which courses each employee followed. It is a good idea to fix the column headings by specifying them in the query properties window.
2. A totals query to calculate total hours for each employee.
3. A query based on the first two, linked on the unique identifier for the employee (EmployeeID or SSN), to combine the information into one result.

You can use this query as record source for a report. You can let the report do the grouping by department. (Of course, department must be a field in the query for this.)

Reply | Quote