Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
 
 
Courses - Microsoft Access 222
Description: Advanced Access Queries, Part 3
Running Time: 128 minutes
Pre-Requisites: Access 221 very strongly recommended
Previous Lesson: Access 221
Next Lesson: Access 223
Main Topics: Action Queries, Update Query, Append Query, Make-Table Query, Delete Query
Versions: This course is valid for Access 2000 through 2003. If you are using Access 2007 or 2010, you will still benefit from this course. You will find that the concepts are the same, but there are cosmetic differences between the versions.

    

Order before 11/24/2019 to get a FREE upgrade to our Access 2010 version!
Click here for details

 
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

 
 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP