Delete Query Join
By Richard Rost
3 years ago
Delete Records that are Missing Related Children
In this Microsoft Access tutorial I'm going to teach you how to create a delete query that can be used to delete records that are missing related records. You will learn about a problem that exists with delete queries that you can't use a JOIN for. We will instead have to use the IN function.
Ethan from Syracuse, New York (a Platinum Member) writes: I am trying to go through my database and delete all orders that don't have any line item details with them. This happens quite often where my sales reps put in orders but don't finish them and don't add line items and now I've got a bunch of basically blank orders in my database.
Warning
Before you begin, moderator Scott reminded me that I should have warned you to backup your database. And of course, I'm not a fan of deleting data, instead I prefer to mark it archived, but there are cases where deleting bogus data is warranted.
Prerequisites
Links
Recommended Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Delete query, could not delete from specified tables, specify the table containing the records you want to delete, delete query multiple tables
Intro In this video, I will show you how to use a delete query in Microsoft Access to remove records from a table when there are missing related records, such as deleting orders that have no corresponding line item details. We will talk about why delete queries with joins do not work as expected in Access, how to identify unmatched records using outer joins and the IN function, and how to set up a proper delete query to clean up unwanted records from your database without running into errors.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, we are going to talk about how to delete records from a table using a delete query that have missing related records in your Microsoft Access database.
Today's question comes from Ethan in Syracuse, New York, one of my platinum members. Ethan says, I am trying to go through my database and delete all the orders that do not have any line item details with them. This happens quite often where my sales reps put in orders but do not finish them and do not add line items, and now I have got a bunch of blank orders in my database.
Basically like this: you have your customers, you have your orders, this one has details, this one has none, it is missing stuff. So we want to go through the database and clean all these out.
Full disclosure, I changed some of the details with this video so that it would have more mass appeal. I wanted to use an example that everyone can understand. One of the tough things that I get sometimes is that people send me questions talking about two tables in their database and I have no clue because it is something specific to their business. So I had to learn exactly what he was doing. It is not orders and details but at least this is something that everyone can wrap their head around without me explaining what these tables mean.
So, orders and line items, two related tables. If you want to learn more about how I built my invoicing database with the order tables, go watch this video.
You should also have a good understanding of relationships. You should also know what an outer join is. It is where you are missing records on one side of a join; you have a customer without a contact, you have an order without a detail item.
You are going to need to know a little bit of SQL, not a ton, not a programmer level amount, just a little bit because there are some things in Access that you cannot do with the query designer and there are other things that you can do but it is easier if you know some SQL. This is one of those things where it is much, much easier if you know a little SQL. And you should understand how delete queries work. If you do not know any of these things, go watch those videos first. They are all free, they are on my YouTube channel, they are on my website, I will put links down below you can click on.
That is why I consider this an expert level class. See over here, expert, expert, it is beyond the basics but it is not quite developer, we do not need any programming to do this. But it is something that I cover in my expert classes because you should know a lot of Access before you get to this point.
Here we go:
Here I am back in my TechHelp free template. You can grab a copy of this free database off my website if you want to. And back to my order form, I have got these orders, got items, this one does not. Let's take a couple of more orders and get rid of their detail items.
Let's go to the customer list, I'll pick John Luke, let's go to one of his orders, we'll delete this stuff. And then we'll do one more. Let's pick Dr. Bashir, orders, and we'll get rid of his vintage track order.
If you have not yet, go through, I took some time and put some pretty cool stuff in here. So you have got some orders in your database. Obviously, with only three it was easy to find them and get rid of them. But let's say you have 3,000 of them, you are 100,000 orders deep in your database. You want to go through and automatically delete all the orders that do not have any items on them.
You could put more checks in there too. You can obviously, if there are no items on them, then the orders will be worth zero. You can double check that, you can make sure that they are not paid, whatever. But I am just going to do the basics: any orders that are missing a related record, a child record.
Normally you would think you could do it with a delete query like this. Create query design, you are going to bring in both of those tables, the order table, the order detail table, bring this thing down here. Now, if you bring in, let's say, order ID over here and order ID over there and you run it. Now you are going to see a bunch of records in here, but what you do not notice is you are missing those orders that we deleted on this side.
If I go back into here and I look for that order ID, okay, so order ID 3 is missing items. And if you look in here, you will notice order ID 3 does not show up at all because this is an inner join. So, in order to see all the orders and the details that match up, we have to change this to a left join.
That is why I wanted you to watch that missing records video. Now if I run this, there is order 3 and order 4, there should be one more down here. There is number 10 for the other one. There are three orders in here that are missing items. This field is null on the side of the relationship.
So now you would think this should be as easy as coming over here and making the criteria down here "is null." I have a video on "is null," by the way, if you are not familiar with it. I assumed if you know all the rest of that stuff, you know what null is, but if you want to learn more, go watch this video.
If I run this now, I can see only the records where this is missing detail items. There are my three orders that do not have any details.
Now what should be just as easy is to turn this into a delete query. Click on that. Usually, when I make a delete query, I teach you to click on this thing first and switch over to datasheet view first because it will show you a preview of the records that it is going to delete before you actually delete them.
That looks good. Come back in here and now run the query. It says, "Specify the table containing the records you want to delete." That is not good. Hit OK. Then you look, we have got two where conditions down here. I forgot to pick a "from." You have to tell it which table you want to delete the records from. That is the "from" condition. There is where and there is from. We cover that in the delete query video.
So pick "from," and wait, hang on, I cannot, why cannot I change that? Confident, from tab, it goes back to where? What is going on there? It will not let you change that. That is weird.
Instead of that ID, let's try the star, order T.* Oh, now it switches to "from." That makes sense. We are getting somewhere. Let's check it again, datasheet view. Good, same three records. We are just seeing all the fields down. That is fine. Run it again. Couldn't delete from specified tables. What is going on?
Let's see, what's next? What can we try next? Let's take a look at the SQL. Click SQL view. OK, delete. It has got two things in here in the delete statement. That is not right. Let's get rid of this second part. We do not want to delete stuff from the order detail table because there is no record there. So, let's just delete that.
Delete order T.* from order T left join, the rest of this is correct. Looks good. Let's take a peek. Looks good so far. Let's go back to SQL view. Everything is good. Run it. Same problem. At this point, I do not know. How about just delete order T? Maybe that'll work. No, specify the table you want to delete from. I do not know. I am at a loss. This is not going to work.
One of the shortcomings with Access is if you have a delete query, it only wants to have one table in here at the top. You do not want to have anything with a join. It causes problems.
That is why the name of the video is "delete query join" because joins, no, that is going to, no, just no.
So how do we do this? Well, instead of using a join, we are going to use the IN function. But in order to use the IN function, we need another query that has the list of records we want to delete.
Let's start over and start from scratch. Let's go back to that first query that we made that had a list of records. It showed me a list of records that we wanted to delete.
Again, create query design. Bring in my order T. Get rid of this thing. Order T, order detail T. Change the join. Looks good. This guy, this guy, is null. Run it. Looks good.
Now, I only want to have one field in here. I do not want to have to deal with order T.order ID and then order detail T or whatever. So, let's get rid of this field from the results. We still need it here for the criteria, but I am going to turn that "show" off. Now when we run it, we get a nice clean list of just the order ID, and that is it. So this is the list of records I want to delete from the order table.
Now, I cannot turn this into a delete query, but I can use this list as the criteria for another query using the IN function.
So let's save this guy. Save this as order no detail Q. I try to keep all my names singular. That is just another one of my little things. Instead of saying "orders without details," this way later on, if I am programming or I am doing something where I have to type it out, I do not have to stop and think if it is order or orders, customer or customers. Just another one of my little tricks that I do.
So we have got order no detail Q. Now, with that in mind, I have got a list here of records I want to delete. Let's make another delete query. Create query design. Start nesting off again.
Let's bring in my order table. No joins in this one now. We are going to turn it into a delete query right away. We are going to bring down the order ID. This is the field that we are checking. Now, we want to make sure that we delete all of the orders where the order ID shows up in this query.
How do we do that? I am going to zoom in so you can see it right in the criteria field here. Press F2 to zoom in. We are going to say "IN" and then in parentheses, you can put a list or you can put a list like 4, 10, 7, whatever. Or you can put a select statement in here.
We are going to say: SELECT order ID FROM order no detail Q. So this is going to run and it is going to return a list of all of the items that come up, in this case the order IDs, and then this will be used as the criteria for that field. So you have got a delete query with no join, but it is using that as the criteria.
Let's take a peek. Look at that. There are the three of them. There they are. Let's go back now and run this query.
I have warnings turned off; you might see a warning that the query is going to run that is going to delete three records. Say OK. Now take a peek in here again and notice they are gone. They are not showing up there. They were deleted. Isn't that cool?
Back in here, let's save this. We'll call this my order delete no detail. Close this guy. Check out the order no detail Q again and nobody shows up.
Go into a customer that you know had one of those types of orders and look, he has got 1 of 1 now. That other record is gone. It is gone.
That is it. Bottom line is if you have a delete query, you cannot have any joins. You have to get your criteria some other way and you can use that IN function to get the criteria.
If you want to learn more about this cool SQL stuff, I have got a three part seminar series that covers all the details. Part one, which actually covers this IN function, is all about selecting data, viewing data, where conditions, order by clauses, all that cool stuff. Everything you really need to know to be efficient with Access without being a programmer. Part two is all about manipulating data: action queries, update, append, delete queries, that kind of stuff. Action queries are things you cannot do in the query by example, the graphical, the visual query designer. And then part three, for those of you who want to get that deep into it, that is for modifying the structure of the database, changing tables, adding indexes, all that kind of stuff, which not a lot of people get that far.
But parts one and two are definitely worth the time if you plan on building Access databases for real. Check out at least part one.
Alright folks, that is going to be your TechHelp video for today. I hope you learned something. I will see you next time. Live long and prosper.Quiz Q1. What is the main goal of the video tutorial described in the transcript?
A. To teach how to update records based on related records in another table
B. To demonstrate how to delete orders with no related line item details
C. To show how to import data into Access from Excel
D. To explain how to create form controls for data entry
Q2. Why is using a simple delete query with a join problematic in Microsoft Access, according to the video?
A. Delete queries cannot work on any tables
B. Access does not recognize SQL syntax with joins
C. Delete queries in Access do not support joins and may result in errors
D. Joins automatically delete related records in both tables
Q3. What is the relationship between the "Orders" and "Order Details" tables in the example?
A. One-to-one, each order matches exactly one detail
B. One-to-many, where one order can have multiple detail records
C. Many-to-many, with multiple orders and details cross-linked
D. No relationship is established between them
Q4. What SQL criterion is used to identify orders that are missing line item details?
A. WHERE OrderDetailID = 0
B. WHERE OrderID IS NULL
C. WHERE OrderDetailID IS NULL
D. WHERE OrderID > 100
Q5. What function does the video suggest using to identify which records to delete when a join cannot be used?
A. The BETWEEN function
B. The SUM function
C. The IN function
D. The LIKE function
Q6. What is the purpose of creating a separate query (like "order no detail Q") before running the delete query?
A. To update the orders table with new data
B. To prepare a list of order IDs that lack detail records for the delete query to use
C. To merge orders and detail tables for reporting
D. To calculate order totals automatically
Q7. When constructing the delete query, how is the list of orders to be deleted supplied as a criterion?
A. Using ORDER BY in the query design
B. Using a join between tables in the delete query
C. Using IN (SELECT OrderID FROM order no detail Q) in the criteria for OrderID
D. Using IF statements in the Access query
Q8. What recommendation does the video make about the naming convention for queries and tables?
A. Always use all lowercase names
B. Use plural names for tables and singular for queries
C. Keep names singular for consistency and easier reference
D. Include the creation date in all names
Q9. What must be true about the structure of the delete query for it to work correctly in this scenario?
A. The query must include at least two joined tables
B. The query cannot include any joins
C. The query must be built using only SQL view
D. The query must use aggregate functions
Q10. What is the role of the warning message Access may show before running the delete query?
A. It prompts you to enter a password
B. It confirms that you want to run an action query that will delete records
C. It previews the query results in Excel
D. It tells you if your query syntax is incorrect
Q11. If you want to delete orders with no related line items, what is a necessary step according to the video?
A. Add a new column to the orders table
B. Create and run a delete query using an IN criterion referencing a separate query
C. Write a VBA script to handle the deletion
D. Use a join in the delete query to link orders and order details
Q12. Which SQL concept helps reveal orders that do not have any matching detail records?
A. Inner join
B. Right join
C. Left join (outer join)
D. Cross join
Q13. When previewing which records will be deleted, what does the instructor recommend?
A. Always run the delete query immediately
B. Switch to datasheet view first to confirm the records to be deleted
C. Use the report view to review results
D. Export the results before deleting
Q14. Why does the instructor suggest watching additional videos about SQL, joins, and delete queries before trying this?
A. Because these concepts are advanced and foundational for understanding the technique
B. Because database design is not relevant to Access
C. Because only programmers need to understand joins
D. Because Access cannot run SQL queries
Q15. What is an example of using criteria with the IN function in a delete query based on the video?
A. IN ("SELECT * FROM order no detail Q")
B. IN ("OrderID=3, OrderID=7, OrderID=10")
C. IN (SELECT OrderID FROM order no detail Q)
D. IN ("Order no detail Q")
Answers: 1-B; 2-C; 3-B; 4-C; 5-C; 6-B; 7-C; 8-C; 9-B; 10-B; 11-B; 12-C; 13-B; 14-A; 15-C
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 TechHelp tutorial from Access Learning Zone covers how to delete records from a table using a delete query when those records are missing related records in Microsoft Access. This is a common situation in database management when you want to clean up orphaned records to keep your data accurate and tidy.
The scenario today deals with orders and line items. Imagine you have a table for customer orders and another table for the line item details of each order. Sometimes it happens that sales reps start an order but never add any items, leaving behind empty order records. Over time, these blank orders can clutter your database.
Although the inspiration for this video came from a specific student question, I decided to use the orders and line items example since it is a case many people can relate to, regardless of your specific business structure.
Before proceeding, you should have a solid understanding of relational tables, relationships, and particularly outer joins. For this scenario, a left join will help you identify all orders, including those without any related detail records. If you need to brush up on any of these concepts, I recommend reviewing those lessons first.
You should also have some basic familiarity with SQL and how delete queries work in Access. You do not need to be a programmer, but knowing a little bit about manipulating queries directly will make this process much easier, since not everything is possible in Access's visual query designer. This particular solution is covered in my expert-level Access classes, so make sure you are comfortable with advanced queries before proceeding.
The challenge is to delete all orders where there are no related line item details. While deleting a few records manually is easy, a real-world database might have thousands of these orphaned orders, and you want to automate the process.
You might think you could just set up a delete query with both tables joined and apply criteria to the detail table's fields. However, using the query designer to build a delete query with joins will create complications. Access expects a single table in delete queries without any joins, or it may throw an error or not delete the records as expected.
The standard approach with inner joins means only orders that have related details will appear. To find orders with missing details, you need to switch to a left join. This way, all orders will be shown, along with any matching details. If the detail fields are null, that order does not have any related items. Applying "is null" as a criterion to the detail fields will filter out these orders with no line items.
When you attempt to create a delete query with this left join, you will run into issues. Access does not allow direct deletion from a table using a query that includes joins. If you try to do this, it may prompt you to specify the table, or it simply will not let you delete the records. Access expects straightforward delete queries to target just one table, without additional joins involved.
The solution is to approach this in two steps: First, create a select query that identifies the orders without details. This query uses a left join to link orders and details, applies the "is null" criterion to the detail fields, and returns just the order IDs of the offending records. You only need the order ID in the results; you can hide other fields.
After saving this query, you create a separate delete query that targets only the orders table with no joins at all. Set the criteria for the order ID field to use the IN function with a subquery that returns the order IDs from your select query. The IN condition tells Access to delete any order whose ID appears in the list generated by your first query.
When you execute this delete query, it should successfully remove only those orders that do not have any related detail items. Once run, those unwanted blank orders are removed from your database.
This IN approach is simple and effective, allowing you to sidestep the limitations of delete queries in Access and avoid dealing with the problems caused by joins. If you want to get comfortable with more advanced techniques like this, I offer a three-part seminar series on SQL for Access. Part one covers selecting and filtering data, including using the IN function. Part two moves on to data manipulation with action queries like update, append, and delete. Part three focuses on modifying the database structure, such as adding tables and indexes for those who want to master database design.
I hope this has helped you understand how to clean up orphaned records with delete queries in Access, even when the built-in tools seem limiting. You can watch a complete video tutorial with step-by-step instructions on everything covered here on my website at the link below. Live long and prosper, my friends.Topic List Deleting parent records without child records using a delete query
Identifying orders without line item details in Access
Using outer joins to find missing related records
Switching join types in Access queries for data analysis
Applying "Is Null" criteria to locate unmatched records
Understanding limitations of delete queries with joins in Access
Creating a select query to list parent records missing details
Saving and reusing queries as criteria for action queries
Building a delete query using the IN function with a subquery
Deleting records based on results of another query in Access |
