Unit 10 . Action queries (IV)


Delete queries

 

Delete queries are queries that remove records from a table.

To create a delete query:

Open a new query in design view.

Add the table from which we want to delete records.

Drop down the Query menu and select the Delete query option

or,

click on the button on the toolbar and select the Delete query option

As from this moment the box changes its aspect, the Show: and Sort: rows have disappeared due to lack of significance here, and in their place the Delete: row has appeared as we see in the next example:

The source of the query can be a table, a query, or a combination of tables. A combination of tables is used when we need to delete records from one table but need the other table for the search criteria.

In the QBE box we only put the field or fields that intervene in the search criteria, and if the query has various source tables, we will put one column to indicate from which table we want to delete the records.

In the Delete: row we can select two options, the Where option indicates a search criteria, and the From option indicates that we want to delete the records from the table specified in this column. E.g:

In this query we delete the invoices (From Invoices) of the clients from California (Where State = "CA").

When the source is only one table the From column is not necessary.

If no search criteria is indicated, ALL the records are erased from the table.

To see the data being deleted before it is deleted we can click on the Datasheet view option on the toolbar, or drop down the View menu and select the Datasheet view option.

To run the query click on the button or drop down the Query menu and select the Run option. On running the query the query deletion from the table is performed although we are advised before that the rows are to be deleted and we can cancel the operation.

Once deleted, the records can not be recuperated.

If the table where we are deleting is related to other tables, the records can be erased from them too depending on the referential integrity rules defined in these relationships. If Access is not able to delete all the records it was supposed to, we will be sent a message advising us that it was not able to as these rules are being infringed on.

 

To practice you can perform Step by step creating delete queries

Unit 10 exercise.

  Unit 10 evaluation test..



 

Page. 10.4

Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited. April-2006.