Results 1 to 2 of 2
2004-04-22, 19:42 #1
- Join Date
- Nov 2002
- South Carolina, USA
- Thanked 0 Times in 0 Posts
Crosstab queries or report? (Access 97)
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.
(names of the courses)
Name.....Job Title.....Gen101....Doc101....Spec101....Spec102.. .total hrs
My report currently has:
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!!
2004-04-22, 19:57 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 Posts
Re: Crosstab queries or report? (Access 97)
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.)