|
This class continues our look at Advanced Query
Techniques. This is part 3 of the series. In this class we
look at Action Queries. What are
action queries? These are queries that allow us to modify, manipulate,
add, delete, and change data in our tables.
There are four basic kinds of action queries:
| Update |
Change data in your table |
| Append |
Add records to a table |
| Delete |
Delete records from a table |
| Make Table |
Create a new table from a set
of data |
Let's say, for example, that you needed to change
all of the 7-digit phone numbers (555-1212) in your database to
10-digit numbers by adding your area code in front of them
(716-555-1212)... You could use an update query. Or, if you
needed to change all of your customer names to UPPERCASE, you
could also use an update query. I'll show you how to do both of these
examples, plus a lot more.
You can use an Append query to take a bunch
of old contact records out of your contacts table and copy them to a
ContactHistory table. Then, you can run a Delete query to
remove them from the original table (you know, to keep your tables small
and efficient). We'll create a project where we can do just that -
archiving old data to another table. We'll make a nice parameter form
that runs everything at the click of a button.
A Make-Table Query is best used to make
automated backups of your tables. Before you modify any table (or run an
update or delete query) you really should
BACKUP your tables! I'll show you how to automate this
process with a Macro.
This is a great class to learn how to
manipulate and modify your data. If you ever work with large
sets of data and you have to make changes to it - even simple things
like changing "ny" to "NY" throughout a table - action queries are for
you!
Now, I really went overboard with this class. It's
over 2 hours long (most of my classes are only an hour). I cover a lot
of different examples - especially with update queries, which is what
you'll use the most. I hope this class helps you work with your data
more efficiently.
|
