Access --> Excel --> PivotTable (Office 2003)

[Maria]

Hello Everybody.

A user has created a 'Select' query in MS Access.

He normally
1/ runs the query (the user is prompted for an 'end' date / filter)
2/ switches to 'pivot table view'
3/ exports to MS Excel

He would like to do this in one step, from Excel.

Any suggestions?

M.

 

[Maria]

Update:

We are almost there

Using the Import External Data feature of Excel, we can generate our pivot
table based on an MS Access Query.

Is there a way to prompt the user for the 'end' date?
Right now I hard-coded the end date into the query for testing, but we would
love the user to have the flexibility to set it himself.

Thx for any suggestions.
Maria.

 

[Jan T.]

Open Excel.

1. Under the Data menu, select Pivot Wizard.
2. In the wizard Step 1, select External data source and click Next.
3. In Step 2, click Get data.
4. Chose MS Access Database and make sure that Use question
Wizard to create and edit queries, is marked.
5. Click OK.
6. Browse, to find your Access database.
7. Select your query and click to get it over to the list box to the
right.
8. Click Next, Sort if you want and click Next.
9. Save Query if you like and chose Return to Microsoft Excel.
10. You returned to the Pivot Wizard Step 3.

Finish the wizard as you use too do it and you now have a dynamic data
source where you can add or delete records. After selecting a cell in the
pivot table and goes to Data/Refresh, the pivot table updates.

If you want to have a dynamic End date, you could try to achieve the in
the query builder in Access by putting a function inside the criteria cell,
i.e. =Date() + 30 (The end date will be 30 days from today).

Good luck!

Regards
Jan

 

[Jan T.]

Well, you can also put in a custom function in your query.
So in stead of putting = Date() + 30 you can put = fEndDate()

Before this will work, you will have to write the function with
vba code in a Standard module in Access. To do that, press
Alt + F11. Insert a standard code Module and write something
like this:

Function fEndDate() As Date
Dim varDate As Variant
fEndDate = Date ' if something goes wrong, use to days date.
varDate = CDate(InputBox("Write the End Date here: ", "End Date (
mm.dd.yyyy )"))
If IsDate(varDate) Then fEndDate = varDate
End Function

Have not tested it yet, put I hope that will help you.

Best wishes
Jan