Make Table Queries
Have you ever run a query and wished that you could save the result set of the query as a permanent table? In Access, that is exactly what the “Make Table” queries do. A “Make Table” query creates a new table as the output of a query, instead of simply displaying a query result set. This new permanent table can be useful in a variety of ways: you could use it as a basis for other queries; it could serve to backup important data; or it could show selected information from multiple underlying tables in a single table which you could then export to Microsoft Excel for spreadsheet-style analysis. Since the basis of the make table query is a standard ‘select’ query-type, you can restrict the columns and rows that are displayed in the new table by picking just the fields you wish to view and applying query criteria.
The fields that comprise the new table created from the make table query’s result set retain the data type and field size properties that were assigned to the original fields. However, other properties that you may have set for the base table’s fields will not be inherited by the new fields. So, you may need to re-enter validation rules, and other properties in the new table, if needed.
To create a make table query, first create a standard query in design view. Select only the fields that you want to include in your new table, and use criteria to restrict the rows selected, if needed. Then click the “Make Table” button in the “Query Type” group of the “Design” tab in the “Query Tools” contextual tab in the Ribbon to change the query type.
In the “Make Table” dialog box that appears, you can type a name for the new table that will be created when you run the query. You can also select whether to place the new table into the current database, or into another database by selecting the desired option button. If you select “Another Database:,” then you will have to click the “Browse…” button and use the dialog box that appears to select the database file into which you want to place the new table. When you have selected where to place the new table, click “OK” in the “Make Table” dialog box.
Once you are ready, you can run the query to create a new table. When you run a “Make Table” query that was based on a “Select” query, Access will delete the “Select” query. So be sure to save a copy before running the “Make Table” query. A dialog box will remind you of this if you forget. Next, you will see a dialog box appear that confirms how many rows you are about to paste into a new table. Click the “Yes” button in the dialog box to paste the selected rows and fields into a new table.
Update Queries
If you want to make large-scale updates to the data in your Access tables based on a specified criteria, you can create “Update” queries to update selected field values based on whether or not the record matches a specified criteria. For example, if you needed to update area codes in a phone number field based on whether or not the phone number contained a specified exchange, you could accomplish that with an update query.
Unlike the ‘select’ queries that we have created so far, when creating update queries all that you need to enter into the QBE grid are the actual fields that you wish to update and the fields that are needed for criteria purposes.
If you do not want to update all of the records in the selected table when you run an update query, then you must specify criteria by which to update the records. That way, when you run the update query you will apply the specified updates to only those selected records that match the given criteria.
To create an update query, first create a new query in design view. Then click the “Update” button in the “Query Type” group of the “Design” tab in the “Query Tools” contextual tab. When you do this, the layout of the QBE grid will change slightly. You will now see a new row appear in the QBE grid named “Update To:.” You then add only the fields from the selected table that you need to update and any fields needed for criteria purposes, and place them into the QBE grid. After that, you enter the value to which you want to update the desired field into the “Update to:” row of the QBE grid, under the field which you wish to update.
Next, you apply the criteria by which you wish to select the records to be updated into the other field or fields, as needed. You can then run the query to begin the update.
Append Queries
You can use append queries as a way of “copying and pasting” records from one table to another table, based on whether or not the records match a specified criteria. You can only append data from table fields to other table fields that share the same (or a compatible) “data type.” So, for example, you may append a “number” field into another “number” field. However, you cannot append a “number” field into a “text” field. The more similar the two fields are, the easier it will be for you to append the data.
If the fields have the same field name, the data is automatically selected to be appended into the field with the same name in the destination table. However, as long as the fields have common “Field Sizes” so you don’t lose appended data, and share the same data types, you can easily specify to which fields the copied data will be appended, even if they don’t share a common name. You can specify into which fields in the destination table the fields from which you are appending will be placed when you create the append query in design view.
To append only selected records from the original table into the destination table, you must use selection criteria in the appropriate fields of the QBE grid when you are creating this query. That way, only the records that meet the specified criteria will be appended to the destination table.
To create an append query, first create a query in design view. Then click the “Append” button in the “Query Type” group of the “Design” tab in the “Query Tools” contextual tab in the Ribbon. Doing this will launch the “Append” dialog box where you can select the name of the table into which you want to append the data from the “Table Name:” drop-down. You can also select whether to append the new data into a table in the current database, or another database by selecting the desired option button. If you select “Another Database:,” then you will have to click the “Browse…” button and use the dialog box that appears to select the database file which contains the table into which you will append the data. When you have selected the table into which to append the data, click “OK” in the “Append” dialog box.
You can then add the fields into the QBE grid that you wish to append into the table that you just selected. Next, click into the “Append to:” row in the QBE grid and select the field in the destination table into which you wish to append the selected field’s data from the drop-down menu of available field choices. In the “Criteria:” row, enter the criteria by which you wish to filter the records which are appended.
After you have select the field to append and into which fields they should be appended, you can run the query to append the data. You will see a dialog box appear, telling you how many rows you are about to append. If this is correct, click “Yes” to continue to append the selected records. If this looks incorrect, you can click “No” to abandon the appending and double-check your criteria.
For more query help, check out our Access tutorial.
