Getting to Know Microsoft Access, Part 6: Advanced Queries and SQL - Delete Queries
(Page 4 of 5 )
Delete queries are very dangerous: always make a backup of the data you plan to delete before you run a delete query! The delete query removes entire records, not just selected fields.
To create a delete query that deletes from only one table, start a new query, choose the table from which you wish to delete records, and choose the Query | Delete Query menu option. Drag the asterisk from the field list to the query grid; the Delete cell now shows "From." Next, drag the field that has the value indicating a record should be deleted into the query grid and enter the appropriate expression in the Criteria cell. Save and run the query to perform the operation. When you create a delete query the design view looks similar to the following:
To delete from multiple tables you will need to run multiple delete queries (assuming the relationship between the related tables doesn’t have the Cascade Delete Records option selected). First you’ll delete the records from the table on the “many” side followed by the records from the table on the “one” side. To do this, choose the Delete Query option as you did previously. Then drag the field to use for criteria to the grid and drag the asterisk from the field's lists of the tables on the “many” side to the query grid. Run the query. Next, remove the “many” side tables from the Query window and drag the asterisk from the “one” table to the query grid and run it again.
Make-Table Queries
Make-Table queries are useful for a variety of purposes including the following:
- Controlling exported information so that confidential data is not included in the new table.
- Using as a record source for a report of events occurring during a specific time period.
- Replacing records in an existing table with new records.
- Exporting information from related tables to other programs such as Excel.
- Exporting records to another database.
You create a Make-Table query by starting a new query, as before, choosing the tables or queries from which you want to take your records, and selecting the Query | Make-Table Query menu option. Next you enter a name for the table you are making. If you choose a table that already exists then any data in that table will be replaced by the new data. Drag the fields you want to put in the new table from the field lists and put them into the query grid. Run and save the query. When you create a make-table query the design view looks similar to the following:
Next: SQL (Structured Query Language) >>
More Microsoft Access Articles
More By Sage Adams
/ 18 
