brainac brainac - 1 year ago 47
SQL Question

Crosstab query does not work in Excel, but works in Access

I have a problem that drives me mad. I have sql query like this:

TRANSFORM Count(Meetings.[ID]) AS [Number of Meetings]
SELECT Format([Meetings].[Meeting Date], 'Mmm') AS [Meeting Month]
FROM Meetings
GROUP BY Month([Meetings].[Meeting Date]), Format([Meetings].[Meeting Date], 'Mmm')
ORDER BY Month([Meetings].[Meeting Date]), Year(Meetings.[Meeting Date])
PIVOT Year(Meetings.[Meeting Date]);

It works fine in Access, but when I execute it from Excel I get no records. Opening recordset part is 100% correct, because it works fine with other queries. Does anybody know the reason why it fetches no records?

Answer Source

If you want to import a crosstab query into Excel from Access you need to jump through some hoops. The crosstab can't be ready by Excel so you should

  • make your crosstab query in Access
  • create a Make Table query that is just Select * into StaticCrossTab from YourCrossTabQuery
  • go back to Excel and import your data as Select * from StaticCrossTab

Kind of a pain but it's the best way I've found to bring cross tab data into Excel from Access. The alternative is to not do the pivot in Access and instead use a pivot table in Excel to create the crosstab you want. Sometimes, depending on the pivot, this is not possible.