Connect Excel to Access - Group By query

[leejayd]

Hi,

I'm trying to connect an Excel data table to the results of an Access query.

This works fine using the "From Access" on the Data Ribbon.

The issue I have is with a query that is a 'group by' type. It isn't possible to select this query from the "From Access" table/view selection. If I make the query a standard select it works fine.

Has anyone come across this? or know of a workaround? I can write VBA to get the data but would rather avoid this.

Thanks in advance.

 

[leejayd]

The plot thickens. I don't think the issue is related to group by. I created a dummy database and a group by query worked fine.

The query I'm trying to consume is rather complex. Sub Queries, CrossTabs and expressions.

I need to determine what is causing the problem.

 

[leejayd]

Okay - I found the issue was related to NZ(). This checks for null in an expression. I need this to overcome nulls in a pivot query.

Removing the nz solves the problem but gives me another problem. Looks like I'll need to resort to VBA