Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    South Carolina, USA
    Posts
    257
    Thanks
    0
    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.

    For Example:
    (names of the courses)

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

    Deborah...trainer.........YES........NO..........Y ES...........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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •