Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2013
    Posts
    163

    Unanswered: Create a Pivot Table from Access Query with Input

    Dear Seniors,

    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...

    Kindly advice

    Thanks and Regards
    R. Vadivelan

  2. #2
    Join Date
    May 2013
    Posts
    17
    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.

  3. #3
    Join Date
    Oct 2013
    Posts
    163
    Hi,

    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.

    Thanks and Regards
    R. Vadivelan

  4. #4
    Join Date
    May 2013
    Posts
    17
    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.

  5. #5
    Join Date
    Oct 2013
    Posts
    163

    Thumbs up

    Hi,

    Thanks your inputs. I had converted my query to append query and followed your steps and I got the results.

    Thanks and Regards
    R. Vadivelan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •