Unit 10 . Action queries (III)


Append queries.

Append queries are those queries that add whole rows to a table.

The new records are added at the end of the table.

We can insert one row or various rows at the same time, normally getting the data from another table, and so an append query has a source (the table or tables where it gets the data from) and a destiny (the table where we will insert the data). The mechanism is similar to that of a make-table query in so far as we define a selection query which permits us to obtain the data to save, and what does vary is that now we have to indicate into which column we want to save every value.

To create an Append query:

We open a new query in design view.

We add the table or tables that we want to extract the data from to store in the destination.

We design the query as we would a normal selection query, in such a way that the data to insert will appear in the result of this query.

We drop down the Query menu and select the Append query... option.

or,

drop down the menu on the toolbar and select the Append query... option.

The following dialogue box will appear:

We type the Table Name where we want to insert the new data to.

The table will normally be in the same database (Current database option) but we can have the table in another database, but for this we need to select the Another database: option and type the name of the database in the File name: box where the table is to be found. It is easier to look for the database with the Browse... button. click on Browse... and the dialogue box will appear to look for the database.

Finally we click on the OK button and return to the Query design window.

The design window will be similar to a selection query, here we define the selection query with which to obtain the data to save in the new table, the only difference is that it has a new Append to: row.

If we open the query properties by clicking on the button on the toolbar we will see the name of the destiny table in the Destination table property and in the Destination DB we see the database where the destiny table is to be found.

 

In the Append to: row we indicate the destination field, i.e in which field in the destiny table we want to leave the value defined in this column.

In the Field: row we indicate the value that we want saved in the destiny field, this value could be a source field, a fixed value, or any valid expression.

We can also include a search criteria in order to select the source records that will be inserted into the table.

When we do not fill in a destiny field, it is filled in with the Default value. In our example nothing is appended to the Start date and Finish date fields as they will be filled in with null (their default value).

When the column has a Autonumber type, we do not normally assign a value to this column so that the system assigns it a value pursuant to the counter, if though we do want a concrete value, we indicate this in the Field: row.

If the destiny table has a primary key and and we try to not assign a value to this field, assign the null value, or a value that already exists in the table, Access will not add the row and you will receive a key infraction error message. For this reason in our example, we will assign the Course code field the value of the [Course code] + 1000 expression so that it does not generate duplicate codes which could produce problems (supposing that the codes in our course table do not reach 1000).

If we have an unique index defined (without duplicates) and we try to assign a value that already exists in the table we will also receive an error message.

If the destination table is related to another, the referential integrity rules will be followed.

 

To practice you can perform Step by step creating append queries.



 

Page. 10.3

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.