Use SQL to quickly create a new table from existing records in Access

SQL Server lets you use one command to quickly create a new table
containing a subset of records from a larger table. For example, suppose in Access you
want to create a phone list for all employees in the Fort Myers region. To do
this, you can create a new table containing only the records of Fort Myers employees in
the central Employee Records database. Follow these steps:

1. Open
   the database containing the Employees table, then click Queries in the
   objects list in the database window.
2. Click New | Design View | OK.
3. Click
   Close.
4. Go to
   Query | SQL Specific | Data Definition.
5. Enter
   the following statement:

   SELECT [Employee ID], [Last Name], [First Name], Phone, City
    INTO Phonelist
    FROM Employees
    WHERE City="Fort Myers"

6. Click
   Run.

Access will insert all employees’ records from the Fort Myers office into the new table called Phonelist.

Miss a tip?

Check out the Microsoft Access archive, and catch up on our most recent Access tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.