Hands on: Delete and Append action queries for your database

Here we will take a brief look at two further ‘action’ queries: Delete and Append. They delete data from, and append data to your table.

The Delete query
A Delete query is perhaps the most terrifying (which is code for ‘practice on a copy of your database first’, as with any action query). With a single mouse click, you can delete all records from a table.

Open a query grid, add the People table and select Delete Query from the popdown list of Query Types. The query grid gains a row labelled Delete: with the entry Where in each column.

Now we compose a straightforward query that identifies all the aardvark-owning males, the SQL for which is:
SELECT People.FirstName, People.LastName, People.[M/F],People.NoOfAardvarks
FROM People
WHERE (((People.[M/F])=”M”) AND ((People.NoOfAardvarks)>0));

Clicking the View button shows the two records that will be deleted when the query is run. If you want to go ahead, click the Run button (the one with the red exclamation mark): Access will check first as it is impossible to reinstate records using Undo.

The Append query
An Append query will add data from one table into another; it can save the huge effort of re-entering data. Our database contains a second table called People2, and we want to add the rows from this table to our original People table: note that there are extra fields in People2 and that several fields have different names.

Open the query grid and add the table containing the rows to be added, click the Query Type button and select Append Query. In the dialogue box, select the name of the table into which the new rows are to be inserted (People), leave the location as ‘current database’ and click OK.

Add the columns that contain data we can accommodate in our original table: those for name, gender and creature ownership. Where field names differ, select the name in the destination table on the Append To: row.

The primary key column in both tables is an autonumber field, and both tables contain rows with the IDs 1, 2 and 3. Just copying these IDs from People2 to People would be impossible because the People table would contain duplicate primary key values – and primary keys must be unique.

Access is clever enough to deal with this automatically: leave the ID field out of the query grid and the rows will be allocated unique autonumbers as they’re appended to the People table. This is the SQL:
INSERT INTO People ( FirstName,  LastName, [M/F], NoOfAardvarks, NoOfPangolins ) SELECT People2.FirstName, People2.LastName, People2.[M/F], People2.Aardvarks, People2.Pangolins
FROM People2;

Clicking View shows the rows that will be appended and clicking Run makes it happen. The database DBCAUG06.MDB contains these queries in un-run form.