Crosstab SQL How to get the Column Heading Values

[Guest]

Hi,

I've writen the following SQL in Excel using DAO:

Transform Count(ComplaintNo)
Select LevelOfComplaint
From tblComplaints
Where ResolvedStage <>'Resolved OR ResolvedStage Is Null
Group By LevelOfComplaint
PIVOT DateReceived

I copy the data from a recordset into Excel, the problem is I need to get
the values for DateReceived for the column headings. How do I do this?

Thanks

 

[Allen Browne]

You would need to OpenRecordset on a query like this:
SELECT DISTINCT DateReceived
FROM tblComplaints
WHERE (ResolvedStage <>'Resolved')
OR (ResolvedStage Is Null)
ORDER BY DateReceived;

Then loop through the records to build the string to use in the PIVOT
clause.

 

[Guest]

Thanks Allen.

You would need to OpenRecordset on a query like this:
SELECT DISTINCT DateReceived
FROM tblComplaints
WHERE (ResolvedStage <>'Resolved')
OR (ResolvedStage Is Null)
ORDER BY DateReceived;

Then loop through the records to build the string to use in the PIVOT
clause.