Excel PivotCharts in Access (1 Viewer)

naa123

New member
Local time
Today, 18:50
Joined
Oct 30, 2023
Messages
20
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

Well-known member
Local time
Today, 19:50
Joined
Feb 7, 2020
Messages
1,991
Queries should be visible and usable even if they do not use form references for filtering and do not use VBA functions.
 

naa123

New member
Local time
Today, 18:50
Joined
Oct 30, 2023
Messages
20
Thank you for your response. This is what I see when trying to link the query - it is only possible to select a table:
 

Attachments

  • 1704891345506.png
    1704891345506.png
    88 KB · Views: 40

Gasman

Enthusiastic Amateur
Local time
Today, 18:50
Joined
Sep 21, 2011
Messages
14,472
Do you have Views set in Options?
1704912371856.png
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:50
Joined
Sep 21, 2011
Messages
14,472
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

New member
Local time
Today, 18:50
Joined
Oct 30, 2023
Messages
20
I see, apologies for my misunderstanding. Yes, I do have View set in options - unfortunately I am still unable to select any queries.
 

Gasman

Enthusiastic Amateur
Local time
Today, 18:50
Joined
Sep 21, 2011
Messages
14,472
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

Well-known member
Local time
Tomorrow, 02:50
Joined
Jan 6, 2022
Messages
1,582
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.

2.png
 

naa123

New member
Local time
Today, 18:50
Joined
Oct 30, 2023
Messages
20
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!
 

Users who are viewing this thread

Top Bottom