4_Structured_queries_method_l3_v1

INSERT QUERY

The INSERT query allows us to add new records to the database. It is analogous to the Create function in our CRUD acronym, however, in the QBE window the INSERT query is accessed via the Append button.

Figure 12

We are going to add a new record to the tblEmployees table using an INSERT action query.

• Open the QBE window and click on the Append button in the ribbon.
• In the append dialog box select tblEmployees:

Figure 13

• In the query Grid type FName:”Tom” in the first row of the first column.
• Select FirstName from the drop down list in the Append To: row.
• In the query Grid type SName:”Cruise” in the first row of the second column.
• Select LastName from the drop down list in the Append To: row.
• In the query Grid type Job:”Drama Teacher” in the first row of the third column.
• Select Job Title from the drop down list in the Append To: row.

Figure 14

• Click the Run button to execute the query.
• You will get a warning that tells you that you are going to add rows to the table Click OK.
• Open the tblEmployees table and you should see that a new record has been inserted with the data we provided.
• Now switch to SQL view to see the SQL code for this query:

Figure 15

To summarise the query:

• The INSERT keyword is followed by the word INTO, this is followed by the name of the table and the fields that we are inserting.
• The second part of the query uses the SELECT keyword and then combines the literal data (“Tom” for example) with as “alias” (AS FName).

Another quirk of MS Access is the way it converts INSERT statements. Another (more succinct) way of writing the above would be:

INSERT INTO tblEmployees (FirstName, LastName, JobTitle) VALUES (“Tom”, “Cruise”, “Drama Teacher”)

The above statement is a more “standard” way of writing an INSERT statement.

UPDATE Query

An update query is used to change a value of one or more fields in an existing table row or number of rows. In our example, we are going to update some of the details relating to the record we have just added. In order to do this we need to somehow identify the row that contains our record.

To do this

• Open the tblEmployees table (we are starting with a fresh copy of the database so you will not see the previous entry).
• Choose a record to edit (we are going to choose the record for Gabrielle Luna).
• Make a note of the EmployeeID for the Gabrielle Luna record.  In our case it is the number 3.

Figure 16

• Close the tblEmployees table.

We are going to use the EmployeeID value with a WHERE clause so that we are certain that we only update the record that we need to.

Note: This is why the WHERE clause is so important. If we run an action query such as UPDATE  without specifying a WHERE clause, we will alter every record in the table.

We are going to UPDATE two of the fields in the Gabrielle Luna record; City and StateProvince. To carry out this update we must do the following:

• Open the QBE editor and add the tblEmployees table.
• Click the Update button on the ribbon.
• In the first column of the query grid select EmployeeID.
• In the criteria row enter the ID value that we have just obtained from the tblEmployeestable (3).
• In the second column select City
• In the Update To: row, type “Sacremento”.
• In the third column select StateProvince.
• In the Update To: row, type “California”.

The query builder should look like this:

Figure 17

Before we run this query, let’s take a look at the SQL code. Select SQL View:

Figure 18

In this command the UPDATE keyword is followed by the table name, the SET clause then tells the database which fields to update and to which value. The WHERE clause comes next and it tells the database to only perform this UPDATE on the row WHERE the EmployeeID = 3.

Run the query and open the tblEmployees table to check that the fields for Gabrielle Luna have been updated.

Note: Again, the parentheses around the WHERE clause are unnecessary.

Скачано с www.znanio.ru