First, go to Access and create the Query, test it, and save it as a query.
Go to Excel, click on Data; Pivot Table and Chart ...; click on External Datasource; click on Get Data; Then click on "New data Source" ...; Click on Okay and give it a name; Then Select "Access" as the driver; click on "Select" and click the file path and Dbname.
From this point you will be able to point to the query within Access - select it, and finish completing the pivot table as normal.
Next time you want to change the query - for example, to a different where clause, or something - you can click on the existing pivot table worksheet, save it under a new name, and then click on the Pivot Table, and click wizard - then click the back button and select datasources, and you will be able to point to the new data source (ie: query)
Note: I have successfully been using the above method for around two weeks, but I am trying to get to a new functionality - I would like the pivot table, the queries to both be dynamic, so that I only need to create one spreadsheet and one query and pass the info to Access to complete the where clause dynamically - I will let you know how that goes. I am very close.
