Help with crosstab (i think?) query please.

[sdp_tws]

Hi, I am hoping somebody can help with a small problem I am having trying to create a query for use in a report. I am sure it must be a crosstab query that I need to create but I'm not sure how it will work.

Currently I have three tables that will be involved, and these are as follows. I have included the fields that are relevent too:

tbl_traingingitems
- trainingitemID(pk)
- trainingiten (text)

tbl_trainers
- trainerID (pk)
- trainername (text)

tbl_trainerlinktable
- trainerID (fk)
- trainingitemID (fk)

These are linked in a query to give me a list of training items, with there relevent trainers beside them. There can be up to four named trainers, so therefore each training item may appear up to four times.

What I want to do is display each training item only once, but add columns for "Trainer #1", "Trainer #2"... etc. And then the trainer's name to be displayed under the relevent column where applicable.

Can anyone shed any light on how to do this please? Any help would be greatly appreciated as I'm tearing my hair out here!

 

[namliam]

Yes crosstab query, just make a normal query with the item, Trainer and count of what they use.
Then use Item as "row header", trainer as "column header" and count as "value" and you should be good to go...

 

[sdp_tws]

Hi, thanks for the reply but that is not exactly what I am looking for. The result of what you are suggest would put the people's names as column headings which is not what I want.

What I require is the column headings to be 'Trainer 1', 'Trainer 2', etc, with the person's name as the actual data.

So it will read something like:

TRAINING ITEM TRAINER 1 TRAINER 2
Lock-up procedure Joe Bloggs John Smith
Data entry procedure John Smith

etc