Help converting Cross Tab to SQL form

[mxfrail]

I am trying to convert this into SQL form -

TRANSFORM count(tblinserts.clientid) AS SumOfHomes
SELECT tblInserts.SalesRep, tblstaff.firstname, tblstaff.lastname
FROM tblInserts, tblstaff
WHERE tblinserts.inserttype<>'W' and tblinserts.salesrep=tblstaff.repid
GROUP BY tblInserts.SalesRep, tblstaff.lastname, tblstaff.firstname
PIVOT Format([ProductionDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


The reason why I need to do this is because I need to beable to get a distinct count off the clientid I am counting.

Any help would be appreciated.

 

[lespaul]

It is SQL...what do you mean convert into SQL form?

Leslie

Have you met Hardy Heron?

 

[mxfrail]

Thats in access SQL but it will not obviously run from SQL 2000 directly because not until SQL 2005 is PIVOT really an option.

 

[PHV]

Have a look at the CASE expression.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886