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.
SELECT [ALL | DISTINCT | DISTINCTROW | TOP number PERCENT]] INTO new-table-name [IN ] FROM >. [IN ] [WHERE ] [GROUP BY column-name. ] [HAVING ] [UNION [ALL] ] [[ORDER BY . ] | IN <[source connect string]>[WITH OWNERACCESS OPTION];
[AS output-column-name] | table-name.* | query-name.* | correlation-name.*>. >
() AS correlation-name | > [OUTER]> JOIN ) AS correlation-name | > ON )
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 .
However, you cannot include a multi-valued field, calculated field, or an attachment field in the .
If you include an IN clause for both the INTO and the FROM clauses, both must refer to the same source database.
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");