Microsoft Access for Beginners - Additional Information
Understanding SQL - Modifying Data
You can read the entire Microsoft Access for Beginners series, completely reviewed and updated for Access 2010, in the new Kindle edition now available from Amazon.com!
Beyond selecting records, 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 or data manipulation 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 very important to double-check your queries before running them. The old saying “Measure twice, cut once” applies. Of course, it’s also good to backup the database every so often.
Here are some of the common action queries you can use in Access and other databases. 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. Without this clause, the query would change all employees to the second shift. 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.
Microsoft Access offers Datasheet View within the Query Builder to 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. Since other database systems like SQL Server don't offer Datasheet View, another way to avoid such mistakes is to write queries as SELECT queries first to see what records will be affected and then changing it to an action query. This is also a good way to get practice writing SQL statements. The example below shows how this would be done with a simple query.
SELECT *
FROM tblPersonnel
WHERE Employee = "104";
Becomes …
UPDATE tblPersonnel
SET Current = 0
WHERE Employee = "104";
JOIN clauses can also be used in UPDATE statements when two or more tables are related and you need to make changes in one table based on an identifying record in another. Here's an example:
UPDATE tblAddresses
INNER JOIN tblPersonnel
ON tblAddresses.EmpKey = tblPersonnel.EmpKey
SET tblAddresses.ZIP = "34482"
WHERE tblPersonnel.Employee = "107";
This query sets the zip code based on the employee number. The two tables are linked on the EmpKey field so the query uses a join to ensure that only the employee record with the correct number will be updated. An INNER JOIN is used to limit the records with the value in both tables.
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 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 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.
Here’s another example where records are copied from one table into another.
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 compatible with the fields in the target table, it will work. The first field in the SELECT clause isn’t even coming from the Import table; it’s the Date function which will provide the current date. Also 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#;
The above query would copy an activity for one lead back into tblAbsences under another date. It uses a join on tblPersonnel to help identify the records to be copied by the EmpKey field. The result is that the query finds records matching employee number 105 for a certain date and copies them, replacing the date with a specified date.
The important thing to remember with INSERT queries that you must supply values for any required fields in the target table or the query will fail when you try to run it.
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/1/2004#;
This query copies all records from Leads where the employee StartDate is earlier than a specified date into a new table called tblPersonnelCopy. The new table doesn't have to exist before you run the query. The query will create it on its own. Access even assigns the correct data types. 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.
Read the entire Microsoft Access for Beginners series offline ...
Microsoft Access for Beginners
Expanded, updated for Office Access 2010 and available from Amazon.com.
External Links:
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
