Access Crosstab query to show two values in each cell?

Question

I have a query that has four fields, TraineeName, TrainerName, TrainingType, TrainingDate.

I want to have TraineeName as row headings, TrainingType as column headings, and both TrainerName and TrainingDate as values. Can I do this?

I want to quickly look and see if a certain employee has had training in whatever area, when and by who. Is there a better way to do this? I want to make this a report that can be printed for audits. I have around 60 employees and 20 training types.

Thanks!!

UPDATE: ADDITIONAL INFORMATION

Table with Employees...  Bob, Bill, Jim, Sandy
Table with Trainers....  Red Cross, Forklifts 'R' Us, Fire Dept
Table with Training....  First Aid, CPR, Forklift Safety, Fire Extinguishers

Table relating the three....

    Employee     Trainer            Training          Date
 1. Bob,         Red Cross,         First Aid,        12/5/11
 2. Bob,         Forklifts 'R' Us,  Forklift Safety,  5/18/11
 3. Bill,        Red Cross,         CPR,              12/5/11

Now I want a report that will list all employees down the side, and all training along the top. Then I can quickly see what employees have been trained, when and by who. so each junction between Employee and Training needs two values. I can add Trainers as another column, but then it will make a new line for each employee/trainer combo.

Table Employees has several fields, but most importantly, EmployeeID(pk and autonumber), EmployeeName

Table Trainers is similar, TrainerID (pk and autonumber), TrainerName

Table Training is similar, TrainingID (pk and autonumber), TrainingName

Table TrainingLog has LogEmployeeID, LogTrainerID, LogTrainingID, LogTrainingDate.

Can you show us a visual output of your data and if possible update your question with table schema as well. anyway, try to think if the data you are trying cross tab can put in a simple pivot table. If it can be, then you can come up with the cross tab for the same. — bonCodigo
@bonCodigo Updated my question as much as I could. Hope this makes more sense now. I may just have to go with dates, as I can use 'Max' as my aggregate function to return the most recent training that the person has had. But I would really like to make a way to see who the trainer was as well. — riley3131
I will create a sample and give you an answer to try out. Now that you have showed your expected output and tables roughly, things are a bit clearer. Can you also tells us about your foreign key, primary key constraints? such as how do you related each table to other? :) — bonCodigo

nicesoni_ash nicesoni_ash · Accepted Answer · 2013-09-17T05:52:49

I know it's posted a long ago but the simplest answer is to create two separate crosstab queries for both your results and then combining them with a normal select query.

Access Crosstab query to show two values in each cell?

1 Answers