Microsoft Access 222 Advanced Access Queries 3 Action Queries: Update Query, Append Query, Make-Table Query, Delete Query, Automate Queries with Macros, More. 128 Minutes.

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:

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.

1. Intro to Action Queries
Different Types of Action Queries
What do Action Queries Do?
Update Query
Append Query
Delete Query
Make Table Query
Always Back Up Your Database!
Always Back Up Your Tables!

2. Update Query
Change a company name to a different one for multiple records
Build an Update Query with the Query Designer
Update To
Criteria

3. Another Update Query
Change company name only for companies in NY
Query to update all uncompleted Tasks to today's date

4. More Update Queries
Change all states to CAPS (ny to NY)
Change all names to CAPS (joe to JOE)
UCase() Function
LCase() Function
Proper Case with StrConv() Function

5. Yet Another Update Query
Length of a string: LEN() Function
Add an area code to phone numbers that are only 7 digits long
Change email domains from @xyz.com to @abc.com
INSTR() Function
Left() Function
Disecting the username from the email domain

6. Append Queries
Create a query to archive your old contacts
Copy all records older than 1 year to an archive table

7. Delete Query
Delete those archived records from the last lesson
Cascade deletes and global relationships
Cascade update related fields
Viewing related records in a table
Delete query also deleting all RELATED records

8. Make Table Query
Making a backup of a table with a Make-Table Query
AutoBackup Query
Exporting just a specific set of data

9. Automated Queries
Make a form to automate archiving contacts
Specify archive date in a form field
Macro with OpenQuery command
Command Button to Run Macro
Record Selectors to No
Navigations Buttons to No
Turning Off Warnings Permanently
Options > Confirm > Document Deletions, Action Queries
Turning Off Warnings Temporarily
SetWarnings Macro Command
MsgBox Macro Command
 

Try a FREE Demo Lesson