MS-Access / Getting Started

---

SELECT . . . INTO Statement (Make-Table Query)

Creates a new table from values selected from one or more other tables. Make-table queries are most useful for providing backup snapshots or for creating tables with rolled-up totals at the end of an accounting period.

Syntax

SELECT [ALL | DISTINCT | DISTINCTROW |
	TOP number PERCENT]] <select-list>
INTO new-table-name
  [IN <source specification>]
  FROM {table-name [[AS] correlation-name] |
   select-query-name [[AS] correlation-name] |
   <joined table>},...
  [IN <source specification>]
  [WHERE <search-condition>]
  [GROUP BY column-name,...]
  [HAVING <search-condition>]
[UNION [ALL] <select-statement>]
  [[ORDER BY {column-name [ASC | DESC]},...] |
  IN <"source database name">
     <[source connect string]>
  [WITH OWNERACCESS OPTION];

where <select-list> is

{* | {<expression> [AS output-column-name] |
  table-name.* | query-name.* |
  correlation-name.*},...}

and where <joined table> is

({table-name [[AS] correlation-name] |
  select-query-name [[AS] correlation-name] |
  (<select-statement>) AS correlation-name |
  <joined table>}
{INNER | {{LEFT | RIGHT | FULL} [OUTER]} JOIN
  {table-name [[AS] correlation-name] |
  select-query-name [[AS] correlation-name] |
  (<select-statement>) AS correlation-name |
  <joined table>}
ON <join-specification>)

Notes A SELECT...INTO query creates a new table with the name specified in new-table-name. If a table with that name already exists, the database displays a dialog box that asks you to confirm the deletion of the existing table before it creates a new one in its place. The columns in the new table inherit the data type attributes of the columns produced by the <selectlist>.

However, you cannot include a multi-valued field, calculated field, or an attachment field in the <select-list>.

If you include an IN clause for both the INTO and the FROM clauses, both must refer to the same source database.

Example

To create a new table that summarizes all sales by product and by month, enter the following (qxmplProductSalesMakeTable):

SELECT tblProducts.ProductName, Format([DateSold],"yyyy mm") AS MonthSold,
  Sum(tblContactProducts.SoldPrice) AS TotalSales
INTO tblMonthSalesSummary
FROM tblProducts
 INNER JOIN tblContactProducts
 ON tblProducts.ProductID = tblContactProducts.ProductID
GROUP BY tblProducts.ProductName, Format([DateSold],"yyyy mm");