how to convert result of an select sql query into a new table in ms access

Question

how to convert result of an select sql query into a new table in msaccess ?

Answer 1

Like so:

SELECT    *
INTO      NewTable
FROM      OldTable

Answer 2

Select *
Into newtable
From somequery

Answer 3

If you want to do it through the user interface, you can also:

A) Create and test the select query. Save it.

B) Create a make table query. When asked what tables to show, select the query tab and your saved query.

C) Tell it the name of the table you want to create.

D) Go make coffee (depending on taste and size of table)

Answer 4

You can use sub queries

SELECT a,b,c INTO NewTable 
FROM (SELECT a,b,c
FROM TheTable
WHERE a Is Null)

Answer 5

First, create a table with the required keys, constraints, domain checking, references, etc. Then use an INSERT INTO..SELECT construct to populate it.

Do not be tempted by SELECT..INTO..FROM constructs. The resulting table will have no keys, therefore will not actually be a table at all. Better to start with a proper table then add the data e.g. it will be easier to trap bad data.

For an example of how things can go wrong with an SELECT..INTO clause: it can result in a column that includes the NULL value and while after the event you can change the column to NOT NULL the engine will not replace the NULLs, therefore you will end up with a NOT NULL column containing NULLs!

Also consider creating a 'viewed' table e.g. using CREATE VIEW SQL DDL rather than a base table.