Another Method For Creating Tables

Select data from an existing table into a new table.

Useful for the following:

– Creating tables and entering data at the same time

– Making backups or partial backups

– Creating archive tables

Exercise: Create a copy of the Suppliers table and call it MySuppliers.

1. Type the following SQL statement in the SQL design window:

SELECT * INTO MySuppliers

FROM Suppliers;

2. Click the View button (Datasheet View) to see the results without actually executing the statement. The query header should be "Query1: Make Table Query" and the results should show 29 rows.

3. Change to SQL view (not Design view) and execute the statement. Click Yes when asked whether you want to continue (only Access gives this kind of warning!).

4. Save the query as "DefineMySuppliers" and close it.

5. Compare the new table with the original in Datasheet and Design view. What, if any, differences do you find?

Exercise: Create an extract of the Categories table without pictures and call it CategoriesNoPix.

(1. Optional: Open the Categories table in design view and note the field names. Close the table before running the SELECT ... INTO ... FROM query.)

2. Type the following SQL statement in the SQL design window:

SELECT CategoryID, CategoryName, Description INTO CategoriesNoPix

FROM Categories;

3. Switch to Datasheet View to see the results without executing the statement.

4. Switch to SQL view (not Design view) and execute the statement. Click Yes when asked about continuing.

5. Save the statement as "DefineCategoriesNoPix" and close the SQL window.

6. Compare the new table with the original.