Question

How to write a query in Prepackaged Access Reports that builds a new table (e.g., use the "Make Table" function in Access)?

Answer

Using the Make Table query type (as shown below), you can make your query select records from the database and save the records as a new table in Access.

1. Open your query in the Design View and click on the "Make Table" function.
   
   
    
2. Name your table (note you can't have a table and a query with the same name).
3. When you run your query for the first time, you'll get a pop-up that says: "You are about to paste xxx row(s) into a new table."
4. If you run the query subsequently, you'll get a pop-up that says: "The existing table "xxxx" will be deleted before you run the query. Do you want to continue anyway?"
5. After you run your Make Table query, you will see it as a Tables Object in the navigation bar.
6. You can then use a different query that utilizes your new table. In other words, your results can be used by more than one query.
7. Note that your Make Table queries will sort at the top of the list of Queries, because they are "different" than normal queries. Also note that they have a different icon than other tables:
    

Using Make Table is helpful if you need to run a query against a table that does not exist, and it is especially useful for BLOB function queries, or any other query which can run for a very long time.

But note that Make Table queries are not necessarily any faster (or slower!) than normal queries.  The difference is in the latter the system will start giving your results right away, but in the former, the system can't show you any results until it has gotten all of them.

An alternative to use Make Table is to use a subquery, which often will fill the same needs.

Additional Information

• Article last edited: 03-Jun-2020