Make Table query.

[Kyle Johnson]

I am using Access 2000, and Excel 2000.

I created a form that updates a make table query, then that query makes
a table from which I have a pivotchart (via excel, because access 2000
does not have pivotcharts). It all works just beautifully for what I
need. However....

Is there a way to make the query make the table before it opens the
excel document? I currently have a button to open the excel document
using a hyperlink (it was just easier) but unless I make the table, the
graphs it makes will be out of date. How would I write an OnClick code
to run the query, have the query make the table (preferably without any
popups being displayed, i did uncheck the Confirm dialog for action
queries), and then open the excel spreadsheet (preferably with a
position relative to the location of the database and containing
spaces.)

Here is my current SQL query, and the other information...

====SQL Query===
PARAMETERS [Forms]![MultiForm]![BegDate] DateTime,
[Forms]![MultiForm]![EndDate] DateTime;
SELECT QC.ID, QC.Date, QC.Overall, QC.Tech, QC.Model, QC.Serial,
QC.Customer, QC.Scanner, QC.Display, QC.Keypad, QC.Radio,
QC.Structural, QC.Description, QC.Failure, QC.Fix INTO [Date Query]
FROM QC
WHERE (((QC.Date)>=[Forms]![MultiForm]![BegDate] And
(QC.Date)<=[Forms]![MultiForm]![EndDate]))
ORDER BY QC.ID, QC.Date;
===End SQL Query===

The table the query is making is called: Date Query
and the excel spreadsheet is located in the same directory as the
database and is: RatesByTech.xls

........
I managed to set up a macro that does the following.. (Dont know how to
copy and paste what code is in the macro... so here is the explanations
of them) I have it running on a GotFocus command, so when the unit is
clicked it opens the query, closes the query, asks the delete the
current table (which is going to scare the user of my database) and
then opens the graph (which displays fine).
===Macro===
OpenQuery (QC Date Query, Datasheet, Read Only) 'Queryname, View, Data
Mode
Close (Query, QC Date Query, Yes) 'Object Type, Object Name, Save
===End Macro===

 

[Jeff Boyce]

Kyle

If you require a table of data before your Excel functions can run, you'll
have to keep "making" it (or updating it by deleting all records and
appending the new ones).

Or, if you based your Excel functions on a query instead of a table, it
would be up to date every time it ran.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Vinson]

I created a form that updates a make table query, then that query makes
a table from which I have a pivotchart (via excel, because access 2000
does not have pivotcharts).

If you're assuming that you must have the data in a Table in order to
create a pivot chart - revise your assumption. You don't.

Rather than a MakeTable query, just create a simple Select query to
select the desired data, and base your Pivot Chart on *that* query.
This will avoid the considerable overhead and bloat caused by overly
frequent creation and destruction of unneeded tables.

John W. Vinson[MVP]