How to save query output as a table??

[John Perry]

What I would like to do is run a query, but then save the query output
as a new table. I can work out how to save the query, but not the
result

How do I do this please?

Thanks

 

[Rick Brandt]

What I would like to do is run a query, but then save the query output
as a new table. I can work out how to save the query, but not the
result

How do I do this please?

Make the query into a Make Table query or use it as the input to a Make Table
query. If you press the "Query" item in the menu bar while in query design view
you will see this option.

 

[John Perry]

Make the query into a Make Table query or use it as the input to a Make Table
query. If you press the "Query" item in the menu bar while in query design view
you will see this option.

Ah thank you. I use SQL, and never use QBE. The solution is to add
at the end of the Select statement "into xxx" where xxx is the name of
the table.

Thanks very much!

 

[deko]

What I would like to do is run a query, but then save the query output

as a new table. I can work out how to save the query, but not the
result

use something like this:

SELECT DISTINCT [DocumentPath], [Folder], [Doc_ID], [Entity_ID] INTO
tblMaintenanceDocuments
FROM tblDocuments;

you can use DDL to modify after it's created:

ALTER TABLE tblMaintenanceDocuments ADD COLUMN Orphan YESNO, Missing YESNO;

add indexes, or primary key:

ALTER TABLE tblMaintenanceDocuments ADD CONSTRAINT pkDocId PRIMARY KEY
([Doc_ID]);

to make sure you get a new table each time, you could run this first:

DROP TABLE tblMaintenanceDocuments;

If you want to save query output in some other format check out the OutputTo
method

 

[John Perry]

What I would like to do is run a query, but then save the query output
as a new table. I can work out how to save the query, but not the
result

use something like this:

SELECT DISTINCT [DocumentPath], [Folder], [Doc_ID], [Entity_ID] INTO
tblMaintenanceDocuments
FROM tblDocuments;

you can use DDL to modify after it's created:

ALTER TABLE tblMaintenanceDocuments ADD COLUMN Orphan YESNO, Missing YESNO;

Thanks for all those tips about enhancements. Now can I ask one more
simple question.

When I select New Query, I always go through a Wizard that comes up
with a QBE screen; it is only a few keystrokes to exit this, but
instead, can I go direct to a blank SQL screen and write it all
myself.

Thanks again!

 

[deko]

When I select New Query, I always go through a Wizard that comes up

with a QBE screen; it is only a few keystrokes to exit this, but
instead, can I go direct to a blank SQL screen and write it all
myself.

wd be nice, but I've never found a way

 

[Larry Linson]

When I select New Query, I always go through a Wizard
that comes up with a QBE screen; it is only a few keystrokes
to exit this, but instead, can I go direct to a blank SQL
screen and write it all myself.

My theory is that Microsoft did not provide that capability because they
didn't want to be accused of promoting masochism. Seriously, it is
difficult for me to imagine why anyone would not start with the Query
Builder (not QBE, that's terminology from othe products) and then go to SQL
if it is necessary -- I've always thought that anyone who _started_ from
scratch with SQL when they did not have to was an "SQL Masochist".

I've used SQL since long before there was an Access, and when Access
provided the very capable Query Builder, I thought, "How nice of them to
provide me with an assistant to write the basic SQL for me."

<GRIN> And, the above is only a little bit "overstated".

Larry Linson
Microsoft Office Access MVP

 

[deko]

My theory is that Microsoft did not provide that capability because they

didn't want to be accused of promoting masochism. Seriously, it is
difficult for me to imagine why anyone would not start with the Query
Builder (not QBE, that's terminology from othe products) and then go to
SQL if it is necessary -- I've always thought that anyone who _started_
from scratch with SQL when they did not have to was an "SQL Masochist".

I've used SQL since long before there was an Access, and when Access
provided the very capable Query Builder, I thought, "How nice of them to
provide me with an assistant to write the basic SQL for me."

<GRIN> And, the above is only a little bit "overstated".

I dunno. It's easier for me to think and type than to click and select.

 

[John Perry]

I dunno. It's easier for me to think and type than to click and select.

I have been using SQL/QMF under IBM's VM operating system and also
under MVS for over 20 years and so think nothing of writing it direct.
The beauty of the "big iron" is that far larger tables with joins can
be used. PC is easier to load the data though.

Sometimes the syntax is a little different, like with saving tables
and Access not being able to have comment lines within a query but
generally, the syntax between "big iron" and PC is very similar.

I've never used the menus. It is all personal choice at the end of
the day.

 

[Larry Linson]

Nice try at a put-down, John. I've known more than a few SQL masochists (a
term I coined back in my own mainframe days). It's not a matter, by the
way, of "thinking nothing of writing SQL directly" or "fearing to write SQL
directly", it's a matter of productivity.

I introduced a number of customers to IBM's mainframe QBE software as a
productivity tool back in its day, and they found it useful -- remember that
one? That was something more than twenty years ago, however. I don't recall
it being RDB, underneath, though, and am sure it did not offer SQL as an
option. Most of the people I sold on using it were not programmers.

Did you ever have occasion to use Application System on the S/370? Like
Access, it could front-end SQL-DS in VM systems or mainframe DB2 in MVS. It
was a Decision Support System, created at IBM's Midland Marketing Center in
Warwick -- one of the last software products I supported in my days as a
mainframer. And, though officially aimed at end-users, most of the customers
who used it created applications with it.

Larry Linson
Microsoft Office Access MVP