Monday, January 18, 2010

Action Queries: Make-Table Query


Action Queries

Action Queries, also known as Data Manipulation Language (DML) statements, do not return a dataset like Select Queries, but makes changes to the data in the base tables. There are four types of action queries: Delete Queries, Update Queries, Append Queries and Make-Table Queries. In previous posts, I discussed the Delete Query, the Update Query, and the Append Query. In this last post of the series, I'll look at the Make-Table Query.

Make-Table Queries

A Make-Table Query is a shortcut method to create a table based on values in another table. In SQL, it's called a SELECT...INTO statement. The basic syntax looks like this:
SELECT ISBN, Title, PubID, Price INTO BooksTemp
FROM Books;

 In the Query Builder:

In properly normalized databases, there aren't a lot of uses for the Make-Table Query. It's most important use is for creating temporary tables. Sometimes, complex queries can be simplified or their performance improved by creating a smaller, temporary table. The Make-Table is ideal for this application.
However, make-table queries do have some disadvantages. First of all, they tend to bloat the database. Every time you make a temporary table, your database grows. When you delete it or over-write it, the database doesn't automatically shrink. This will require that you periodically compact your database.
Secondly, you don't have a lot of control over the structure of a table made with a make-table query. These tables automatically inherit the field datatypes and sizes from the parent table, but it does not inherit any other field properties (like validation rule or defaults), nor does it inherit any indexes.

Running a Make-Table Query

There are two buttons for running Action Queries: The View button displays the results of the query, that is, which records will be updated. The Run button actually appends the records to the target recordsource.
.

2 comments:

Mike said...

I just tried this funtion in order to create a temporary table. I am trying to integrate Access 2010 with Outlook 2010 to generate emails.

My intention was to create and populate a table from a SELECT statement, then link the table to Outlook, then generate emails.

Unfortunately, I received the following error: "Multi-valued fields are not allowed in SELECT INTO statements."

Any suggestions for an alternative route?

Roger's Access Blog said...

Mike,

This really isn't the best venue for support. I suggest a good web forum like www.UtterAccess.com .

Nevertheless, my first suggestion would be to do away with the multi-valued field. It's a bad idea that Microsoft should never have implemented. It seems like a good idea when you first create the database, but later on, you run into more and more things you CAN'T do with MVFs.

Avoid them like the plague.