Microsoft Access for Beginners - Additional Information
Understanding SQL - Modifying Data
In the first article on SQL, I focused on the SELECT command and how you can retrieve data from your database. Beyond selecting data, SQL is also used to change data when necessary. With some very simple statements, you can make changes to large numbers of records or very precise changes to a single record. Using some of the same clauses that you would use for SELECT statements, you can be very specific about what changes you make.
SQL statements that change data are often called action queries. The most important thing to remember with action queries is that they cannot be undone. Once the data is changed, the only way you're going back to the original data is if you have a backup of the database. For this reason, it's every important to double-check your queries before running them and, of course, to backup the database every so often.
Here are some of the common action queries you can use in Access. You can test each one of the sample queries in the demo database.
UPDATE queries
With UPDATE queries, you can change a single record or large numbers of records with a single statement. The syntax is a little different than the SELECT statement but still pretty straightforward.
UPDATE tblPersonnel
SET Shift = 2
WHERE Employee = "104";
The first line specifies the table to be updated and the SET clause uses a simple expression to set the Shift field to 2. The WHERE clause is important here as it limits the records on which the change is made. If you were to forget this clause, you could easily change all employees to the 2nd shift with this query. In a really large table, you might find yourself wondering why the query is taking so long and then realize with a sinking feeling that you'd forgotten the vital WHERE clause.
Access offers Datasheet View within the Query Builder which will show you the results of the query before it's executed and it's a good idea to get in the habit of using this. Another way to avoid such mistakes is to write queries as SELECT queries first to see what records will be affected before changing it to an action query. This is also a good way to get practice writing SQL statements. In this case:
UPDATE tblPersonnelSELECT *
SET Shift = 2
FROM tblPersonnel
WHERE Employee = "104";
You can also use JOIN clauses in UPDATE statements when two or more tables are related and you want to make changes in one table based on an identifying record in another. Here's an example:
UPDATE tblAddresses
RIGHT JOIN tblPersonnel
ON tblAddresses.EmpKey = tblPersonnel.EmpKey
SET tblAddresses.ZIP = "34482"
WHERE tblPersonnel.Employee =
"107";
In this case, the query is updating the zip code field in tblAddresses based on the Employee number from tblPersonnel. The two tables are linked on the EmpKey field so any records from tblAddresses that correspond the record in tblPersonnel with that Employee value will be updated. A RIGHT JOIN is used because normally the join would start with tblPersonnel which supplies the EmpKey value and end with tblAddresses which uses it. In this case, however, I'm updating tblAddresses and looking to tblPersonnel for the correct Employee value.
Thinking of LEFT and RIGHT for table relationships can be confusing but it may help if you compare it to reading a sentence from left to right, and think of the parent table (the table that supplies the value for the field that's being linked on) on the left. As you move to the right of the relationship, you see the child tables that use the field as a foreign key to link to the parent table. A LEFT JOIN would therefore be a join that starts from the left of the relationship (tblPersonnel in the above example) and a RIGHT join would start from the right (tblAddresses). Note the word OUTER is optional in both relationships.
DELETE queries
DELETE queries are used to delete records from specific tables and again, I will emphasize that these queries cannot be undone so it is important to carefully check the query before executing it. The old saying "Measure twice, cut once." is very appropriate here.
The syntax for a DELETE query is actually very similar to a SELECT query so it's easy to use the method I mentioned earlier of creating the SELECT first.
SELECT *
FROM tblSkills
WHERE EmpKey = 6
You can change this to a delete query by changing one word:
DELETE *
FROM tblSkills
WHERE EmpKey = 6
As with SELECT and UPDATE queries, you can join on other tables to determine which records will be selected for deletion.
INSERT INTO (Append) queries
With SQL, you can insert new records into tables, using either a combination of values assigned to the fields within the table or records from another table. You can even copy records from a table back into that same table, changing selected values as needed.
INSERT INTO tblDepartments
(DeptNo, DeptName)
VALUES "016", "Legal"
In the query above, the INSERT INTO clause names the table that will receive the records with the second line specifying the field names. The VALUES clause specifies the values to be entered in the same order as the field names.
The above query will run as shown but if you switch to design view and then back to SQL view, you may see extra text added after the specified values; "AS Expr1" is added after "016", etc.. These are referred to as aliases and are used as shortcut references to elements within a SQL statement. Access SQL assigns these aliases to values that are manually entered like the ones above. Aliases can also be assigned to tables as shortcut references for long table names.
INSERT INTO tblAbsences (AbsDate, PayStatus,
Hours, Reason, Comments)
SELECT AbsDate, PayType,
Hours, ReasonGiven, Notes
FROM tblImport;
The above is an example where records from an import table are being imported into tblAbsences. The names in the import table are slightly different but so long as the data types of the fields are the compatible with the fields in the target table, it will work. Notice that after the INSERT INTO clause, the rest of the query is an ordinary SELECT statement. It could even use a WHERE clause to limit the records being transferred from tblImport.
INSERT INTO tblAbsences (EmpKey, AbsDate,
PayStatus, Hours, Reason, Comments)
SELECT EmpKey, #10/5/2004#,
PayStatus, Hours, Reason, Comments
FROM tblAbsences
LEFT JOIN tblPersonnel
ON
tblAbsences.EmpKey = tblPersonnel.EmpKey
WHERE
tblPersonnel.Employee ="105"
AND
tblAbsences.AbsDate = #10/4/2004#;
This query copies a record from tblAbsences back into the same table but with a different date for the absence and it uses a join on tblPersonnel to help identify the record to be copied by the Employee number. The result is that the query finds one record matching Employee 105 on 10/4/2004 and copies that record, replacing the date with 10/5/2004.
Notice the hash marks (#) on either side of the literal date value in the last query. These are called delimiters and are used by Access SQL to separate a literal date value from the rest of the SQL statement. Quotation marks (" or ') are used for string values.
MAKE TABLE queries
Sometimes you might want to export data from one table to another. One way to do this is to use a Make Table query. By this time, you'll find the SQL statement pretty familiar.
SELECT *
INTO tblPersonnelCopy
FROM tblPersonnel
WHERE tblPersonnel.StartDate =#8/11/2004#;
This query copies all records from tblPersonnel where the StartDate is 8/11/2004 into a new table called tblPersonnelCopy. The new table doesn't even have to exist before you run the query. The query will create it on its own. Access even assigns the correct data types and, in this case, marks the EmpKey field as an AutoNumber in the new table. The only difference between this and a SELECT query is the INTO clause on the second line.
Conclusion
There are additional types of queries available in Access that enable you to retrieve and analyze data in different ways but the four mentioned here should give you an introduction that you can build on as you learn to write your own queries. Learning how to write SQL independently of an interface is an important step in realizing the power available to you when working with Access or any other database.
For more information:
Access SQL: Basic Concepts, Vocabulary and Syntax - From Microsoft Office Online, a series of articles on using SQL to work with data in Access.
10 Tips for Using Wildcard Characters in Microsoft Access Criteria Expressions - From TechRepublic by Susan Harkins
