Distinct count in Crosstab

[souljacked]

I have a bog standard crosstab query that looks like this;

Jan Feb Mar Apr
Area1 6 9 21 11
Area2 9 14 26 18

Column heading - [Call Date]
Row heading - [Area]
Value - [AgentName] which I am counting.

This data is being grabbed from a query called PULLCALL.

An agent name can have many calls in a month but I want to count them uniquely. For example Tom, Bill, and Rob all make 2 calls each in March - my crosstab shows 6, as thats the amount of calls made. I would like it to show 3 - the amount of agents that made calls.

Any idea how I would go about doing this?

Thanks in advance.

Tao

PS Sorry for the lame first post.

 

[ajetrumpet]

more than likely you are going to have to use something like:

SELECT DISTINCT COUNT(agent field)

instead of counting the calls. you have to put the DISTINCT keyword in manually. you cannot create it with the CT wizard.

 

[souljacked]

Thanks but that didn't seem to work. I'm no expert but I don't think transform is compatible with DISTINCT.

Not that I even know what transform does

This is my code

TRANSFORM Count(PULLCALL.[ID&V Score]) AS [CountOfID&V Score]
SELECT PULLCALL.Area
FROM PULLCALL
GROUP BY PULLCALL.Area
PIVOT Format([Call Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Any help would be very much appreciated.

P.S I am now counting ID&V Score rather than agent name as access didn't seem to like that field.