Running Access queries to populate pivot table?

[dmcgimpsey]

Hi everyone, great resource.

I have developed an interface that has a main menu that uses hyperlink
to select one of many static excel pivot tables. There ar
approximately 20 worksheets.

The tables themselves are populated from hard coded Access queries tha
return specific data for each pivot table. The only difference betwee
each Pivot Table, and between each Access query is that there is on
'where' field (clause) that distinguishes each query / pivot table.

For 2003 there are 20 worksheets, and now, for 2004, there will be a
additional set of worksheets.

What I would like to do, if possible, is to have one pivot table, tha
can dynamically select the access query that it needs to run and retur
the data to a 'null' worksheet or template that has been set up. I us
the word template and hope I don't confuse the issue, perhaps "model
would be a better word. It is an empty pivot table, set up to receiv
the data from any query.

Even better would be to select a generic query and pass the 'where
parameter to it - if possible. For example, when the user selects
value, that value is passed to Access and Access uses it in the 'where
clause. Example: the user selects "Toronto" for city, and the valu
"Toronto" is uses in the "where city="Toronto"' clause in access.

I would also like to have the query selection via a drop down list bu
that is optional, and for the scope of this thread, is not s
important.

Before I post, I want to compliment the people on this board for th
excellent posts, and threads; this is a valuable resource and I hav
learned a lot just from reading threads through sequentially.

Regards

Don

 

[onedaywhen]

The tables themselves are populated from hard coded Access queries that
return specific data for each pivot table. The only difference between
each Pivot Table, and between each Access query is that there is one
'where' field (clause) that distinguishes each query / pivot table.

You need to create a 'stored procedure' (I think they are called
'stored queries' in MS Access-speak) in the database. Below is an
example:

CREATE PROCEDURE
MyStoredProc (
start_date DATETIME,
end_date DATETIME
)
AS
SELECT
RefID,
DateEffective,
Earnings
FROM
EarningsHistory
WHERE
DateEffective
BETWEEN start_date AND end_date;

--