Introduction

Welcome! Action, Make Table & Union Queries

S M L XL FS | Slo Reg Fast 2x | Bookmark | Autoplay: ON

Welcome to Microsoft Access Expert Level 16. In this course we will continue our in-depth study of action queries in Access, covering advanced topics such as running multiple queries with a macro, setting warning messages off, using delete queries across multiple tables, make table queries for backups, and handling mailing lists with multiple parents. We will also discuss union queries to merge results from different tables, review previous lessons, explain recommended learning practices, and outline the structure of the course and available student support resources.

Navigation

Prev: X16
Next: Lesson 01

Keywords

Access Expert, action queries, update queries, append queries, delete queries, make table queries, archive contacts, macro run queries, disabling warnings, delete with criteria from another table, backup table, multiple parent mailing list, union queries

Subscribe to Introduction
Get notifications when this page is updated

More Information

Transcript

Welcome to Microsoft Access Expert Level 16 brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. Today's class continues our in-depth study of action queries.

We have already learned how to build update queries, append queries, and delete queries. In the first lesson today, we are going to make a macro to run multiple queries at once. We will make a single button that you will click on to archive old contacts in the system. It will run an append query to copy those old records to a backup table, an archive table. Then it will delete them out of the primary contact table. We will learn how to set warnings to off so that Access does not bother you with all those prompts.

Next, we will learn how to use delete queries with multiple tables, where you want to delete records in one table with criteria based on a value in a different table. For example, let's say you are not doing business in a state like California anymore. You want to delete all of the contacts from the customers who live in California, but you still want to keep the customer record. So you are deleting values from the contacts table based on a value in the customer table.

Next, we are going to learn about the fourth type of action query, make table queries. A make table query is used to create a brand new table. We will use a make table query to back up our customer records. Anytime you want to make a change to the customer table or the data in the customer table, you can just click a button, and it will automatically make a backup copy that you can have in the current database or in a backup database somewhere else. I will show you how to do that.

We will then use our knowledge of make table queries and append queries to handle a common problem that people ask me how to deal with. This is the multiple parents for a child mailing list problem. Let's say you have a list of students, and you have two parents listed on the student record, parent one and parent two. Perhaps they do not live together. So you want to be able to send mailings to both of them, but doing so is very difficult. In this lesson, I will show you a technique, actually two techniques, to deal with that problem.

Finally, we are going to learn about union queries. Union queries let you merge the results from two or more tables or two or more queries to look like a single query. For example, earlier we pulled apart our contacts table. So we have all the old contacts in an archive table somewhere else. With a union query, we can temporarily put them back together so you can search through all the contacts.

This class follows Access Expert levels 13 through 15. In 13 through 15, I covered the other types of action queries: update, append, and delete queries. I strongly recommend you learn how to work with those before continuing on with today's lessons.

This class was recorded using Access 2013. All the lessons should work just fine with 2007 or 2010. If you are using Access 2003 or earlier, you will want to go to my website and get the Access 2002 to 2003 lessons. Those cover action queries for the older versions of Access.

My courses are broken up into beginner, expert, advanced, and developer level classes. Beginner level classes are for novices. You should understand all the topics covered in them by the time you get to the expert level classes that you are in now. When you finish all the expert level classes, the advanced classes will cover event programming and macros, and the developer classes will cover Visual Basic for Applications. Each group of classes is broken down into multiple levels: level 1, 2, 3, and so on.

In addition to my normal Access classes, I also have seminars designed to teach specific topics. Some of my seminars include building web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders and running a service business, tracking accounts payable, learning the SQL programming language, creating loan amortization schedules, and lots more. You can find details on all of these seminars and more on my website at AccessLearningZone.com.

If you have questions about the topics covered in today's lessons, please feel free to post them in my student forums. If you are watching this course in the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class video. Here you will see all of the questions that other students have asked, as well as my responses to them and comments that other students have made. I encourage you to read through these questions and answers as you start each lesson and feel free to join in the discussion.

If you are not watching these lessons on my website, you can still visit the student forums later by visiting AccessLearningZone.com/forums.

To get the most out of this course, I recommend you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually, create the same database that I make in the video, step by step. Do not try to apply what you are learning right now to other projects until you have mastered the sample database from class.

If you get stuck or do not understand something, watch the video again from the beginning or tell me what is wrong in the student forum and I will do my best to help you.

Most importantly, keep an open mind. Access may seem intimidating at first, but once you get the hang of it, you will see that it is really easy to use.

Now I strongly encourage you to build the database that I build in today's class by following along with the videos. However, if you would like to download a sample copy of my finished database file, you can find it on my website at AccessLearningZone.com/databases. Sometimes, if you get stuck, the easiest way to learn is to tear apart someone else's database.

One of the ways that I taught myself Access years ago was by tearing apart the Northwind Traders database that comes with Microsoft Access. You will find there is a sample database for each of my courses on my website.

Now let's take a few minutes and go over exactly what we are going to cover in today's class.

In the last class, Access Expert 15, we created a couple of queries to archive old contacts from our contact table to a contact archive table.

In lesson one, we are going to go over the homework from that class, which was to create a single button that runs both of those queries and disables the warning messages.

In lesson two, we are going to discuss the proper technique for running a delete query involving multiple tables, where the records that you want to delete are based on a criteria from another table.

In lesson three, we are going to learn about make table queries as we can set up a query to create a new table based on other data.

In lesson four, we are going to set up a multiple parent mailing list. What does that mean? We have a single student table, where you have the student's first name and last name. But then you have two parents in the same record, parent one and parent two.

We need to be able to sometimes send multiple mailings out so that each parent, who might not live together, gets separate mailings. So how do we set that up properly in our database so we can create that mailing list?

In lesson five, we are going to learn about union queries, which allow you to take two or more tables or other queries and put them together so it looks like one set of records.

Now, sent to the library.

Intro

Quiz

Q1. What is the primary benefit of using a macro to run multiple queries in Access?
A. To automate several actions with a single button click
B. To make queries run faster individually
C. To prevent data from being deleted
D. To create tables automatically

Q2. When archiving old contacts, what is the correct sequence of actions?
A. Append old records to an archive table, then delete them from the primary table
B. Delete records from the primary table, then append to archive
C. Update records, then make a copy
D. Just export records to Excel

Q3. What does setting warnings to off in an Access macro accomplish?
A. Prevents Access from displaying prompts for each action
B. Automatically repairs broken databases
C. Secures the database against unauthorized users
D. Reduces the size of the database

Q4. How can you delete records from one table based on criteria from a different table?
A. Use a delete query with a join or subquery referencing the other table
B. Only delete records from the referenced table
C. Manually select records in both tables
D. Use a form to filter records before deletion

Q5. What is a make table query used for in Microsoft Access?
A. To create a new table based on another query or table's data
B. To update records in place
C. To delete all records in a table
D. To create relationships between tables

Q6. Why might you use a make table query before making changes to a customer table?
A. To create a backup copy of the original data
B. To remove duplicate records only
C. To sort the table before editing
D. To combine tables

Q7. What is the "multiple parents for a child mailing list" problem described in the lesson?
A. Sending mailings to both parents of a student who may not live together
B. Sending mailings to students only
C. Preventing mailings from being sent to parents
D. Only including one parent per record in mailings

Q8. Which query type allows you to merge results from two or more tables or queries?
A. Union query
B. Update query
C. Delete query
D. Crosstab query

Q9. Why is it recommended to watch the whole lesson before doing anything on your computer?
A. To get an overview and understand the process before following along
B. To ensure your computer does not crash
C. Because Access cannot run queries while videos play
D. So you do not forget the instructions

Q10. Where can students download a sample finished database file for the course?
A. AccessLearningZone.com/databases
B. NorthwindTraders.com
C. Microsoft.com/AccessDownloads
D. Online forums only

Q11. Which Access versions does this course specifically apply to?
A. Access 2013, 2010, and 2007
B. Access 2003 only
C. Access 365 only
D. Excel and Access both

Q12. What should you do if you get stuck while working through a lesson?
A. Watch the video again or ask questions in the student forum
B. Skip ahead to the next topic
C. Close the database and try a different course
D. Use an external SQL editor

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone takes us deeper into the world of Microsoft Access, specifically action queries, at the expert level. I am Richard Rost, your instructor, and in this lesson we are expanding on what you already know about update, append, and delete queries.

Our first lesson for today is all about automating a common database task. We will create a macro that allows you to run multiple queries with just one button click. For example, when it is time to archive old contacts from your contact table, you will be able to move those records to a backup table using an append query and then delete them from your primary table with a delete query. I will show you how to suppress the usual warning prompts that Access throws up during this process so the job runs smoothly and without interruption.

Following that, we will examine how to use delete queries across multiple tables. Many users want to know how to delete records in one table based on criteria found in a different table. For instance, if you stop doing business in a certain state, like California, you may want to delete all of your contacts who live there, while keeping the main customer records intact. This lesson will show you the correct method for building these kinds of queries.

Next, we will explore the fourth type of action query: make table queries. A make table query lets you generate a brand new table using data from existing tables or queries. This is particularly useful when you want to back up records, such as your customer data, quickly and with the click of a button. I will guide you through setting this up so you can create a backup copy in your current database or even save it to a different one.

We will then combine our skills with make table and append queries to solve a common issue faced by many Access users: mailing lists with multiple parents per child. For example, if you manage a student list and store two parents (parent one and parent two) in separate fields for each student, and those parents live at separate addresses, it becomes tricky to print mailings for both. I will show you not one, but two methods to handle this and make sure each parent receives mailings as intended.

To wrap up today's class, we are going to look at union queries. These are very powerful for merging records from different tables or even different queries into a single set of results. For example, after archiving some of your contacts to a separate table, a union query can temporarily bring your current and archived contacts together so you can search or report on the complete list as one.

This session builds upon everything covered in Access Expert Levels 13 through 15. If you have not mastered update, append, or delete queries by now, I encourage you to go back and review those lessons first.

All of today's examples were created with Access 2013, but everything should work with Access 2007 or 2010 as well. For users on Access 2003 or earlier, I have special lessons designed for those versions on my website.

My courses are designed for a range of experience levels. At this point, as an expert student, you should be comfortable with everything from my beginner courses. After you complete all the expert courses, you will be ready for my advanced topics, where we tackle event programming and more advanced macros. Then in the developer track, we get into Visual Basic for Applications. Each set of classes is divided into levels to help you progress at your own pace.

Besides my main curriculum, I also offer specialized seminars on topics such as building web-based databases, calendar-style forms and reports, database security, working with images and attachments, service business management, managing accounts payable, SQL programming, loan calculations, and much more. You will find all the details about these on my website.

If you have questions about any of today's topics, I encourage you to visit my student forums. If you are following along with this course on my website, each lesson has its own forum section next to the video where you can see what other students have asked and join the discussion. I invite you to read through the questions and add your own if you need any help. Even if you are watching elsewhere, you can find the forums at AccessLearningZone.com/forums.

The most effective way to learn is to watch each video lesson fully before you try anything on your own. Then, watch it again and work alongside me, building the same sample database in step with the video. Don't try to apply these concepts to your own projects until you have mastered them in the class exercises. If you get confused or stuck, rewatch the video or ask for help in the student forum, and I will do my best to assist.

Remember, Access can seem a little overwhelming at first, but with patience and practice, you will find it quite manageable.

You can also download a sample of the finished database from my website if you want to explore a working example. Sometimes, breaking apart someone else's work is the fastest way to learn. That was how I taught myself Access years ago, by taking apart the Northwind Traders database that ships with the program.

To give you a roadmap for today, here is what we will be covering:

- In the last class, Access Expert 15, we created queries to archive old contacts.
- In lesson one today, we will review the homework from that class, making a single button run both the append and delete queries, with warning messages disabled.
- Lesson two covers the best way to use delete queries involving multiple tables, deleting records based on criteria found in another table.
- In lesson three, we learn about make table queries and how to use them for quick backups.
- Lesson four is about multiple parent mailing lists - how to set up your tables so two parents on a student record can get separate mailings.
- Lesson five explains union queries, which allow you to pull records from multiple tables into one unified query.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Creating a macro to run multiple action queries at once
Building a button to archive old contact records
Appending records to an archive table
Deleting archived records from the contacts table
Disabling warning prompts for action queries
Using delete queries with multiple tables
Deleting records based on criteria in another table
Make table queries to create backup tables
Backing up customer tables with make table queries
Techniques for handling multi-parent mailing lists
Mailing list setup for students with two parents
Using union queries to combine multiple tables or queries
Searching through archived and active records with union queries

Article

Welcome to this Microsoft Access Expert tutorial, where we will continue studying action queries, one of the most powerful features of Access for automating tasks and maintaining your database. We are going to dive deeper into working with update queries, append queries, delete queries, and introduce make table and union queries. You will learn how to combine these queries using macros, automate workflow processes such as archiving contacts, handle multiple table criteria in queries, tackle complex data models like multiple parent mailing lists, and merge records from different sources for reporting or searching.

To start, imagine you need to archive old contacts from your contact table. Perhaps you want to remove any contacts older than a certain date. The process involves two steps. First, you use an append query to copy those old contacts to an archive table, preserving them for reference. Second, you use a delete query to remove those records from your main contact table so it stays current.

Running two queries every time can quickly become tedious, so we will automate this using a macro. In Access, macros let you combine multiple steps into one button click. To do this, create a macro and add the OpenQuery action twice, first for the append query, then for the delete query. Between those, set warnings to off using the SetWarnings action. This stops Access from displaying confirmation prompts for each step. At the end, turn warnings back on. Now, add a button to your form and link it to the macro. Clicking the button will archive and delete your old contacts with no interruptions.

Often, you need to delete records in one table based on criteria in another table. For example, suppose you have a customers table and a related contacts table. If your business stops operating in California, you may want to delete all contacts associated with customers from California, but not remove the customer records themselves. To accomplish this, create a delete query using your contacts table and join it to the customers table on CustomerID. In the criteria row under the state field in the customers table, type California. Make sure only fields from the contacts table appear in the delete query grid, since you are removing contacts and not customers. When you run this query, only contacts linked to customers from California will be deleted, leaving your customers table intact.

Next, let us look at make table queries. A make table query creates a new table from the results of your query. This is useful for making backup copies of your data before making major changes, or exporting a consistent snapshot for another system. For instance, before making a big update to your customers table, you can create a make table query that selects all records from customers and outputs them to a new table called CustomersBackup. Design the query based on your customers table, and in the Query Type menu select Make-Table. Specify the new table name and click run. If you want to back up to an external database, you can specify another Access file. This is a simple and effective way to protect your data as you make changes.

Now, let us address a common challenge: creating mailing lists for children with two parents. Imagine you have a students table with fields for StudentID, FirstName, LastName, Parent1Name, and Parent2Name. Sometimes, parents might not live together, but you need to ensure that mailings go to both. The problem is, generating a mailing list where each parent gets their own letter is tricky with this structure. One technique is to use a union query that combines parent1 and parent2 into one list.

First, create two select queries. One query selects student and parent1 information, ignoring parent2. The second query does the opposite. Then, write a union query by switching to SQL view and typing:

SELECT StudentID, FirstName, LastName, Parent1Name AS ParentName FROM Students WHERE Parent1Name Is Not Null
UNION ALL
SELECT StudentID, FirstName, LastName, Parent2Name AS ParentName FROM Students WHERE Parent2Name Is Not Null;

This query will produce a combined list where each parent appears as a separate row, with the student info repeated as necessary. Now, generating mailings or labels for each parent is straightforward.

Finally, let us explore union queries further. Union queries are useful when you want to merge results from two or more tables or queries so they appear as a single dataset. For example, if you have moved some old contacts to an ArchiveContacts table, but occasionally need to search or report on all contacts, you can use a union query to combine the Contacts and ArchiveContacts tables. Again, switch to SQL view and type:

SELECT * FROM Contacts
UNION ALL
SELECT * FROM ArchiveContacts;

This combines the two tables into a single, temporary view for searching or reporting, even if the underlying data is split between tables.

To summarize, today you learned advanced techniques for automating multiple action queries with macros, deleting records in one table based on another, safely backing up your data with make table queries, handling many-to-one relationships like multiple parent mailings, and combining data from separate tables using union queries. You can implement these steps in any Access database, saving time and allowing your database to scale with more complex requirements.

If you ever get stuck or want to review, build a sample database as you follow these examples. Practice each technique step-by-step until you feel comfortable. Remember, exploring sample databases and looking at how others have solved similar problems is a great way to learn. Experiment, ask questions in Access communities, and do not be afraid to tinker - Access is a powerful tool, and mastering these skills will make you far more productive.

Primary Topics

macros with action queries, append queries, delete queries with multiple tables, make table queries, multiple parent mailing lists, union queries, disabling warnings

Secondary Topics

contact archiving, backups, user forums, sample database access