| |
| |
|
Courses - Microsoft
Access 222 |
| Description: |
Advanced Access Queries, Part 3 |
| Running Time: |
128
minutes |
| Pre-Requisites: |
Access 221 very strongly recommended |
| Versions: |
|
We use Access XP in this course,
but the lessons are valid for all versions of Access from 95 to 2003.
There are cosmetic changes in Access 2007. Order before 3/14/2010 to
get a FREE upgrade to our 2007 version when released!
|
|
|
|
|
| |
 |
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. |
|
|
|
AC222 Major
Topics |
- Action Queries
- Update Queries
- Append Queries
- Make-Table
Queries
- Delete Queries
|
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.
|
Access 222 Outline
|
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 |
| |
| |
|
 |
Huge Discounts Available
When you purchase multiple classes together
Huge savings up to 50% off! Order Now. |
|
| |
Student Interaction:
Microsoft Access 222
|
Richard on 1/1/2007:
Action Queries: Update, Append, Make-Table, and Delete Queries. Automate multiple queries with a Macro. More! This is a HUGE class - action queries are one of the most powerful features of Access! |
|
Bob B on 10/26/2007: Rick,
I'm in Access222 and I have created an "Update1Q"and when I try to run it from the Query menu I continue to get a alert message that I am about to update 0 row(s). I say yes and nothing happens. When I double click on Update1Q again the same thing happens. Why? |
|
Richard Rost on 10/26/2007: "About to update 0 rows" means that your query has nothing to do. Check your parameters and make sure they are correct. You can temporarily convert the query back into a SELECT query (just change the query type) and run it to see what data it's working on. If there is nothing for the query to change, you'll see the ZERO rows message. |
|
PAUL TIBI TENDO on 7/31/2008: THIS IS THE THIRD TIME I AM GOING OVER THE LESSON. I FIND THIS LESSON VERY INTERESTING WITH LOTS OF IMPORTANT INFORMATION |
|
Amy Roell on 8/2/2008: How do you update data from two tables, but still keep some criteria in the Main table field column?
Main table needs to have all current data and only update if something is new like shipping information.
|
|
Richard Rost on 8/23/2008: Amy, you would have to use the DLOOKUP function to check and see if any of the information changes. If so, a little AfterUpdate event could correct it. We'll start covering this stuff in the 300-level classes. |
|
Tina on 12/1/2008: I have just watched the 222 class with update query. You had the update query change the DueDate if completed was false. This got me thinking that there must be a way to have a message box will pop up if I change the payment type to the words “Credit Card” on my customer Transaction subform within my main customer form. The message box will remind me to change the customers payment from cash to credit card on the main customer form. Like a update query but with out running a query just having a message box pop up if the words “credit card’ is typed in the subtable payment section |
|
Richard Duckworth on 3/20/2009: Macro: SetWarnings - where is this action in Office 2007 |
|
Richard Duckworth on 3/20/2009: Update Queries: where can we go to learn about the "millions" of examples you referenced in this tutorial? |
|
Richard Rost on 3/23/2009: Richard, they've hidden a lot of the lesser-used functions. In the Ribbon, Macro tab, in the Show/Hide group, click on the "Show All Actions" button. Now you should see it in the list. Another bone-headed Access design change for Microsoft. |
|
Richard Rost on 3/23/2009: I don't remember the lesson. Was I being sarcastic or exaggerating? :) |
|
Bill Gillock on 4/20/2009: Richard
Re: Update Query
I was wondering if you can use an SQL UPDATE command in VBA like you do with the SELECT command and pass variables and field values to the UPDATE command.
thanks.
|
|
Richard Rost on 4/21/2009: Bill, you can use any valid SQL statement with the DoCmd.RunSQL command - including update, append, and delete commands. |
|
|
| |
| |
|
You may want to read these articles from the 599CD Blog: |
|
| |
|
 |
| |
| Do you have
questions about
Word, Excel, Access, Web Design, or computers in general? Just
ask us anything you'd like.
Click here
for assistance. |
|
|
|
|
|
| |
|
CLICK HERE for a FREE
lesson |
Order
your first 599CD course now.
Your Satisfaction is Guaranteed!
|
|
|
|
|
|
| |
|
|
|
|
