Crosstab SQL How to get the Column Heading Values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DWDZEB40 - 07 Nov 2006 09:57 GMT

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

Reply to this Message

Allen Browne - 07 Nov 2006 10:02 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I've writen the following SQL in Excel using DAO:
>
[quoted text clipped - 9 lines]
>
> Thanks

Reply to this Message

DWDZEB40 - 07 Nov 2006 10:23 GMT

Thanks Allen.

> You would need to OpenRecordset on a query like this:
> SELECT DISTINCT DateReceived
[quoted text clipped - 19 lines]
> >
> > Thanks

Reply to this Message