Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
 
 
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.

    
 
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

 
 
 

 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 3/28/2024 10:22:42 AM. PLT: 0s