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 tblPersonnel
SET Shift = 2
SELECT *
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
Sams Teach Yourself SQL in 10 Minutes (3rd
Edition)
(Available on Amazon.com)