Does anyone know if this would even work? I have a table that contains multiple records for each customer. Each record is distinguished by the Edate value such that CustID would have Edate values (yyyymm) of "200501", "200503", etc for only those months where an event actually occured for that CustID. Only one event would occur for a custID per month. The crosstab query simply spreads the columns by Edate month, and by various rows depending on preference (A/B category, date acquired, whatever, etc). All works fine, but the actual counts for customer types is overstated since it's counting records, not unique customerIDs. Thus if I have 100 "A" customers each with 6 monthly records and 50 "B" customers each with 5 monthly records, the counts grouped A&B would show 600 "A" customers and 250 "B", but it should be 100 and 50 respectively.
TRANSFORM Count(tblPTET.CustID) AS CountOfCustID
SELECT tblPTET.Edate, Count(tblPTET.CustID) AS [Total Of CustID], tblPTET.Edate AS [Min Of Edate]
FROM tblPTET
GROUP BY tblPTET.Edate
PIVOT tblPTET.RDate;
I tried various select statements but end up with a "multi-level GROUP BY claus is not allowed in subquery" error:
TRANSFORM Count(tblPTET.CustID) AS CountOfCustID
SELECT tblPTET.Edate, Count((SELECT Count(*) As CountDistinctCID FROM (
Select Distinct Int(CustID) As dCID
From tblPTET ))) AS [Total Of CustID], tblPTET.Edate AS [Min Of Edate]
FROM tblPTET
GROUP BY tblPTET.Edate
PIVOT tblPTET.RDate;
thanks
