Access 2007-2013
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  
 
NEW Release: Access Advanced Level 4 and Level 5   dismiss
 
 
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 4/26/2017 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

 
 
 

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.
 Ayman Hama on 5/6/2010: Can we update multiple recods in the update query at one time

Reply from Richard Rost:

Yes. The whole point of using an update query is that you can update as many records as you want by setting a criteria. For example, change all records where the state is "NY" to "PA", for example.

Mubeezi Micah on 5/18/2010: Dear Richard,

I am trying to design a letter report to be sent to a patient as a reminder in case they miss their appointment for review. I managed to write ="Dear " & [PtName] & "," in the Dear section of the letter.
My challenge is: I want to pick only the first name in the PtName field and i want it to be in proper case. In your Excel classes, i leanrt that if say i had Richard Rost in cell A1, i would use a formula like =PROPER(LEFT(A1, SEARCH(" ",A1,1))) to peel off Richard. Where can i place a similar function in Access (using StrConv in this case as demonstrated at 10:40 of this video)in order to get the first name only? My guess is that it should be possible in Ms Access.

My question is rather long. I hope you will understand me

MICAH

Mubeezi Micah on 5/18/2010: Dear Richard,

I have gotten the answer to my question in the next video (video 5). I will use the Instr and then the left function.

Thank you for the great work you have done. Very soon i will share with you my database that i am building.

MICAH

ariel martin on 7/7/2010: If you add data on the original table, does it automatically update the data that the maketableqry produced?

Reply from Richard Rost:

No. You'll have to re-run your make-table query to refresh the data.

 Debbie on 4/29/2011: Where is the function guide you mentioned at around 4:25 in Lesson 222, part 5?

Reply from Richard Rost:

Honestly, I haven't put one together yet! I forgot all about it. I have one for Excel, but not Access. I should get on that! Thanks for reminding me.

Sambit Kundu on 5/16/2011: Hi Richard, First of all thanks for such nice and detailed teachings which helped me to understand Access better. I have one table of Bank Gurantee for customers of a company where I need to issue notice letter on the basis of Bank Gurantee Due date. With the help of automated query I could solve upto Notice Query. How can I generate reminder letters for all the customers to whom notice has been served but no reply.

Thanks in anticipation of a positive reply.

Reply from Richard Rost:

The easiest way to do this would be to create two boolean (y/n) values in your table: NoticeServed and HasReplied. You can turn the NoticeServed values ON when you generate your letters the first time (an update query would work fine). Someone will have to check the box for HasReplied for the people who do reply. Then, just make yourself another query where NoticeServed=YES and HasReplied=NO and use that to create your letters.

 Elaine Mason on 8/24/2011: I am following up on a question that was asked by Debbie in April of 2011 about the function guide. Is it up on the Website yet.
Benjamin Chua on 10/17/2011: Richard LOL!!! Regarding Backup your Database BEFORE running any Action Queries.

Class 222 Video 1 time 4:44

Benjamin Chua on 10/17/2011: Richard,
yes I am interested of you showing me how to do action queries on commissions. class 222 video 9 time 25:20

i am not sure if this was covered in the 300 class since this video is very old. i'm alsmost there in 300.

learning quickly - great videos!!!

Jing Zhao on 5/15/2012: How can i move my old record to another table after i add a new record? Like, creat a history table.

Reply from Richard Rost:

Do you mean like archiving old information? That's coming up with append and delete queries. Keep watching (lessons 6 and 7 should answer your questions).

Err on 5/17/2012: Can someone tell me please how can I make a macro or any other way to back up my database whenever I close the application? I want to have an updated backup at all time.
Thank you!

Anny H on 7/7/2012: I imported a table and I only have access 2003 so I can't import to much data in so I split the information into 14 different tables. I want to append the tables together but I'm not getting the pop message that I'm moving so many fields to the new table I created with the copy and paste with no data only the fields. Is there anything that I'm doing wrong?

Reply from Richard Rost:

Wow... how big is your table!? Even with Access 2003, you should still be able to have 2 GB in a database. I have Access 2000 on one of my machines and it routinely works with 1 to 2 GB tables.

Don't use copy and paste to move that much information. Use an APPEND QUERY like I show you in class.

Colette on 2/14/2013: Hi Richard, I enjoy listening to your classes. I have a question on queries. How to update a field in one table based on a field from another table.
PAUL TIBI TENDO on 12/8/2013: the courses are so good that I AM REPEATING THE COURSES AGAIN. I HAVE GAINED A LOT FOR THE COURSES.

PAUL TENDO
CAMEROON

Reply from Richard Rost:

Glad you enjoy them.

Alex Hedley on 1/13/2016: Missing Calendar Control Forum Post
Emad on 2/10/2016: Kindly, I Loved it!! I want to see more action queries examples plz. Thanks!!

Reply from Alex Hedley:

Have you seen X13 or the SQL Seminars?

Emad on 2/10/2016: Hello, Kindly, How can I put Warning Msgbox in Macro to tell me"Are you sure you want to Archive", with YES or NO Button?

Reply from Alex Hedley:

I don't tend to use Macros and aren't in front of my PC to check but

RunCode: MsgBox("My Message", 4)

 

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
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