Excel PivotCharts in Access

[naa123]

Hi,

I am trying to create a PivotChart in Excel using data from my Access database as an external data source. I will then link this PivotChart back to my Access database. However, when I try to create the connection to the Access database in Excel, I am only able to select tables as the data source. I would like to select a query instead. Does anyone know if this is possible?

If anyone has any alternative suggestions on how to create PivotCharts in Access 2016, it would also be good to hear these.

Thanks!

 

[ebs17]

Queries should be visible and usable even if they do not use form references for filtering and do not use VBA functions.

 

[naa123]

Thank you for your response. This is what I see when trying to link the query - it is only possible to select a table:

 

[Gasman]

Do you have Views set in Options?

 

[naa123]

Do you have Views set in Options?
View attachment 111842

Hi, apologies for the silly question - can you tell me how you got to this Table Options menu? I cannot see this in Access 2016.

 

[Gasman]

It is in Excel? as you said

I am trying to create a PivotChart in Excel using data from my Access database

So in 2007, it is the Data tab, then From Other Sources, then From Microsoft Query.

 

[naa123]

I see, apologies for my misunderstanding. Yes, I do have View set in options - unfortunately I am still unable to select any queries.

 

[Gasman]

I see, apologies for my misunderstanding. Yes, I do have View set in options - unfortunately I am still unable to select any queries.

So is that due to the restrictions mentioned in post #2?

 

[KitaYama]

In Office365, PivotTable-> From External Database doesn't show views (at least for me)

This is the way I always do it.
Click Data tab
Click Get Data -> From Database -> From SQL Server Database
Type your server name and Database, then follow the wizard
After the wizard is finished, you will see a list of views and tables.
Select a view and select Transform Data button
Your view will appear in Power Query Editor. For now, because it's your first try, don't edit anything.
Click Close And Load in toolbar and import your view into Excel.
Now you can create a pivot table based on this imported view.

 

[naa123]

In Office365, PivotTable-> From External Database doesn't show views (at least for me)

This is the way I always do it.
Click Data tab
Click Get Data -> From Database -> From SQL Server Database
Type your server name and Database, then follow the wizard
After the wizard is finished, you will see a list of views and tables.
Select a view and select Transform Data button
Your view will appear in Power Query Editor. For now, because it's your first try, don't edit anything.
Click Close And Load in toolbar and import your view into Excel.
Now you can create a pivot table based on this imported view.

View attachment 111854

This is really useful, thank you very much for your help!