CODEWITHSUNDEEP
sqlexcelvbams-access
brainac
brainac

Reputation: 410

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?

Upvotes: 0

Views: 943

Answers (2)

SDS
SDS

Reputation: 1

Create the crosstab query in Access (myCrossTabQuery) and then in Excel import using Select * from myCrossTabQuery

Upvotes: 0

Brad
Brad

Reputation: 12245

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.

Upvotes: 1

Related Questions