Turn a query into a table

[John T Ingato]

If I run a query that calculates the sales data for all the inventory level
in a table, how do I go about saving that sales data into a permanent table?
I plan I using this sales data often, and running the query each time is
time consuming.

Is there a way to create a new table and import the query data into it?
What about the next time I run the query, can I just append the new data
into the table I just created? Can both of these be done with one query, or
do I need two queries; one that creates the table and imports and one for
just the import?

Is there a way to test for the tables existance first? If table exist then
import, else create table and import.

[Allen Browne]

In query design, choose Make Table from the Query menu.

Once you have the table, you can use an Append query instead of a Make
Table.

Instead of dropping and recreating the table, you can delete all records
with a Delete query.

If you want to automate this in a macro, use RunSQL.
The Execute method is more powerful if you are okay with code.
Details:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

If you leave the temporary table in place, you don't need to worry about
whether it exists, but if you want to do it anyway:
Not IsNull(DLookup("ID", "MSysObjects", "([Type] = 5) AND ([Name] =
'Query1')"))

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John T Ingato" <jt******@hotmail.comwrote in message
news:12*************@corp.supernews.com...

If I run a query that calculates the sales data for all the inventory
level in a table, how do I go about saving that sales data into a
permanent table? I plan I using this sales data often, and running the
query each time is time consuming.

Is there a way to create a new table and import the query data into it?
What about the next time I run the query, can I just append the new data
into the table I just created? Can both of these be done with one query,
or do I need two queries; one that creates the table and imports and one
for just the import?

Is there a way to test for the tables existance first? If table exist
then import, else create table and import.

[Bob Quintal]

"John T Ingato" <jt******@hotmail.comwrote in
news:12*************@corp.supernews.com:

If I run a query that calculates the sales data for all the
inventory level in a table, how do I go about saving that
sales data into a permanent table? I plan I using this sales
data often, and running the query each time is time consuming.

First build your select query, maKe sure it's working right.
Make a copy to work on, so you don't lose everything if yu make
a mistake. Open the query in design mode. Right-Click in the
query. From the popup menu that appears, select query type->
make-table query. Enter a name in the dialog box. Save the query
and run it. Your new table has been created and filled with the
results of the query. If you run it a second time, it'll ask to
overwrite the existing table or cancel.

Is there a way to create a new table and import the query data
into it? What about the next time I run the query, can I just
append the new data into the table I just created? Can both
of these be done with one query, or do I need two queries; one
that creates the table and imports and one for just the
import?

Some data manipulation will benefit from filtering the data to
the current week and using an append query to add the current
week's data to an existing table. You build an append query
similarly to the make table, (just choose append query instead
of make-table query from the dialog) with one extra step. A new
row will appear in the design grid where you choose the column
name in the destination table.

>
Is there a way to test for the tables existance first? If
table exist then import, else create table and import.

Yes, but doing this requires visual basic code.

>

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com