Unanswered: Create a Pivot Table from Access Query with Input
I am using Access 2013 for my project work and created a queries with some user input (Say Cut off Date). I saved the query in access and tried to create a pivot table in Excel using "Use an External Data Source" option.
When I select the option only Table is displaying in the dialog box and query is not appearing for selection. But it is not the case for a normal query.
Could you advice me how to create a Pivot table from a query having user input?
Or I need to export the query results to Excel and create a pivot table from there...
You can do it many different ways but here is one way of doing it. You can link a table value or values to data cells and have the excel pivot table build off the values. So first you would need to have a select query to find the data you want. Furthermore create a table and append the new data to it whether it be counts or sums. Now through excel you should be able to to link the table you just created with the data by going to the Data tab and Get External Data then find the Access DB your using. If you do it right you should be able to choose from tables to link to specific cells. You can insert a pivot table into excel and predetermine the cells you want to use for your excel data series. If I didn't explain it well enough just let me know where your getting caught up. Good Luck.
Could you please explain me in detail, I understand that I need to get the table data s in excel and make a pivot table from the values..Please correct me if I am wrong..
Actually my requirement will be solved If I export a query results to a file saved in particular location.
I have created pivot table in a particular file and saved it. Now I want the values to get updated based on my query input. I have created a macro and it works fine, but whenever I run the macro it creates a new file.
Do you have the select and append queries to first get the data stored into a table. If so you just want to go into excel and insert a pivot chart. After the choose cell range(A1-C1 or A1-A3 depending on if your reading by row or column) you want to have that be your series for the pivot table(Could be any range really not just what I listed). Next you go to the Data tab and at the top left it says get external data, from there you will have to find the database your working in. You link the cells to the specific table/s and everytime you run the queries in access the new data is put into the tables in access and it will update in the excel sheet , in return rebuilding your pivot table with fresh data.