Replace Crosstab query counts with "X"

[Smitty]

I have a simple crosstab query that matches Training Classes with Job Titles.

All it does is list the job title as rows and Classes as column headers. Any matches currently return a 1 using a count on the class ID. This may seem trivial, but how can I replace the 1 with an "X"?

I tried using an expression expr1:Iif([CourseID]=1,"X","") but no luck.

Thanks,

 

[revans]

I don't do much with xtabs. My first thought is to create a second simple select query based on the original xtab query and include your iif... field substituting 1 for X there.

hth,

Rich

 

[Smitty]

Good idea, I'll give it a shot.

 

[boblarson]

I just tested revans' idea and it worked. I used this for my first query:

SELECT [01_Jobs].JobDescripton, [01_Classes].ClassName, [01_TrainingJobs].DateAttended
FROM (01_TrainingJobs INNER JOIN 01_Jobs ON [01_TrainingJobs].JobID = [01_Jobs].JobID) INNER JOIN 01_Classes ON [01_TrainingJobs].ClassID = [01_Classes].ClassID;

And then used this for my crosstab:

TRANSFORM Max(IIf(IsNull([DateAttended])," ","X")) AS DateAtt
SELECT qryForJobsTraining.JobDescripton, Max(qryForJobsTraining.DateAttended) AS [Total Of DateAttended]
FROM qryForJobsTraining
GROUP BY qryForJobsTraining.JobDescripton
PIVOT qryForJobsTraining.ClassName;

And it worked. I wish I had a way to upload a pic somewhere so I could show you but I don't currently have the ability to do so since I'm at work.

 

[Smitty]

Thanks Bob!