Cross Tab queries in MySQL ???

[LittleSmudge]

I have a simple CrossTab query that runs fine in MSAccess 2000 which is

TRANSFORM Count(CRId) AS CountOfCRId
SELECT Month(DateReceived) AS TheMonth,
Count(CRId) AS TotalOfCRId
FROM tblCorrReg
GROUP BY Month(DateReceived)
PIVOT CorrType;


tblCorrReg is the table
CRId is the Prime key
DateReceived is of type Date
CorrType is of type SmallInt and currently can be [Null | 1 | 2 | 3 | 4 ]


The above causes a syntax error when I use it as the rst.Open " .. .. SQL string to open a recordset on the MySQL database.

I've now ported the data to a mySQL database but I still need to run the CrossTab query in the Access Front End.

How do I do crosstab queries in MySQL ?




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[BNPMike]

I've put a question on one of the Access Forums. I suspect when Access tries to build an ODBC request to MySQL, it is not sophisticated enough to issue a select and then turn the results into a Transform.

I don't think MySQL implements Transform natively.

 

[BNPMike]

Please see:

http://www.tek-tips.com/viewthread.cfm?SQID=785527&SPID=181&newpid=181&page=1