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.

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.

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.