Delete Query
By Richard Rost
4 years ago
Delete Query: Delete Records from Tables in Access
In this Microsoft Access tutorial, I'm going to teach you how to use a Delete Query to delete records from tables. We'll do a simple delete based on one table first, then we'll do a more complex delete query involving criteria from two tables.
Pre-Requisites
Recommended Courses
Links
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, #fasttips, delete query, Create and run a delete query, How to Create a Delete Query, How to Delete Data using a Delete Query, Delete Queries, delete query using two tables
Subscribe to Delete Query
Get notifications when this page is updated
Intro In this video, I will show you how to use a delete query in Microsoft Access to safely remove temporary or unnecessary records from your tables. We'll cover the importance of backing up your data before running delete queries, selecting records to delete based on specific criteria, and handling more advanced scenarios such as deleting records across related tables. I'll provide step-by-step instructions and tips to help you avoid common mistakes when working with delete queries in Access.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I'm going to show you how to delete records from your table using a delete query in Microsoft Access.
Now, a delete query is the most dangerous of all of the action queries: make table, append, and update queries. Delete is the most dangerous because you can wipe out data. So before continuing, back up your data. You should be backing up every night as it is. Anytime you do any major work on your database, make a copy of it. Click and drag, drop, copy, whatever you need to do.
If you want to learn more about how to properly back up your Access database, go watch this video. It's free. It's on my website. It's on my YouTube channel. Go watch it. Back up your database. I don't want any complaints like "Rick told me to do this and I clicked that and I did a delete query and all my stuff's gone." No, back up your data. Go do it right now while you're thinking of it, while you're watching this video. I want you to go back up your stuff now because you know you haven't done it in six months. Go.
I will also tell you that I'm a fan of not deleting any data. Don't delete stuff. Anything that has business meaning: customers, orders, order details, quotations, contact history, products, anything. If it has an actual meaning for your business, don't delete it. Archive it. Save it. Mark it inactive. Anything like that. Go watch this video. I'll explain that in much more detail.
Also, before we get started, I want you to know what Access query criteria are. You'll need to specify criteria to be able to pick which records you want to delete in your delete query. Go watch this video if you don't know what criteria are.
Now, with all that being said, sometimes you will need to delete some data, really temporary data. For example, when I do an email blast, I have a query that runs and it gathers up, let's say, I'm sending out an email to all my Microsoft Access students. The query runs some updates and some appends and it builds a nice little temporary table with all the email addresses in it. I send out the email blast. I can delete that then. There's no real importance to that.
Log data: sometimes old log data from your website, or if you keep an audit trail in your database, you don't need to keep that very long, maybe a year. So there are some things that you will delete, but they don't have any real business meaning after a while.
So how do we actually delete this stuff? We use a delete query. Just like we use a select query to pick records, an append query to add records, and an update query to update records, a delete query is used to delete records.
Now, I'm going to pretend the data that I'm deleting isn't of any use, but I'm just going to use the tables that I have. Of course, you would not use real world tables for this. I've got a customer table with a list of my customers, and I add a bunch of new ones. I've got a contact table that is basically every time you talk to a customer, that's called a contact. This is the contact history for each of these customers.
Let's pretend I want to delete all of the contacts in the database that are before 2022. Basically, all of these ones. In a simple situation like this, I could just select those and hit delete. Let's pretend I've got 100,000 records in here and they're in no particular order. That's why we would use a delete query.
Go to Create - Query Design. This is going to start off just like a select query. You're going to bring in the table that you want: contact table. You're going to set up the criteria. For example, in this case, the contact date is going to be less than 2022-01-01. Yes, I use ISO dates because I have students all over the world, and an ISO date is readable by everybody: year, month, day. You want to learn more about it? There's a video. Go watch that.
If I run this, you'll see the records. I haven't turned it into a delete query yet. These are the records that will be deleted, just so you can verify. Come back here. Now, we're going to switch this to a delete query. When you do that, you'll see this changes a little bit. It's a delete query where you've got the criteria here, and now I can run the query.
I'm going to click on it. Ready, go. Nothing appears to happen because I have my system warnings off. If you watched my blank database setup with the TechHelp free template, you'll know that one of the things I do is turn off warnings for action queries. It's under File, then Options, then come down to Client Settings, and it's right here: action queries. I leave those off. I find them annoying. You might see a message saying, "We're about to delete eight different records. Are you sure you want to do that?" Say yes.
Now, if I go into my contact table at this point, you'll see they're gone. They're deleted. If you want to get them back, you're going to have to restore from your backup because there's no way you're getting them back out of Access.
I'm going to restore my database because I want to do another example for you. We're going to close this one. Save changes? No. Delete this guy, and I'll copy my backup and drop my backup here. I got all those records back. That's why you make your backups. Let's verify that those records are back. There they are.
Delete queries are a little trickier if you want to delete based on two tables or more. For example, let's say I want to delete all those old contacts, but only for customers that are inactive. In my customer table, I've got a field in here called IsActive. Let's say I mark more people inactive. I'm inactive too. Their customer is inactive, which usually means they're dead or they moved out of your area. They're not customers anymore.
I'm going to keep the customer record, obviously, but I don't need to have their old contact history. I don't care about all their calls and stuff like that. Let's run a query to delete all of the contacts that are from before 2022 and the customer is inactive. This is where these get tricky sometimes.
Go to Query Design. We're going to bring in CustomerT. We're going to bring in ContactT. Notice the join there. That's fine. If you don't know how to do joins, go watch my relationships video. I'll put links to all this stuff down below. You can click on these. They're all free videos.
I'm going to pick from my customer table. I want the IsActive field. Set a criteria: IsActive has to be False. I don't want active customers. Then come over here. Now we're going to find the contact date again: less than 2022-01-01.
Let's see what this looks like. It's still a select query. These are the contacts that are going to be deleted. Be careful, because right now we're dealing with records from two different tables here: customer table and contact table.
Now we're going to switch this over to a delete query. We've got two criteria down there. If I try to run this query now, look what happens. It says specify the table containing the records you want to delete. This is very important. Be very careful.
Hit OK. What you're going to do is bring down the star from the table you want to delete the records from. If you bring down the wrong one, you're going to delete the wrong records. You would delete all the customers that have contacts that are old. Don't do that.
We want to bring down the star from the contact table. In the delete row it says "From." We're deleting from the contact table where these two conditions are met.
So now, before I run this, I'm going to do a quick check just to make sure. I have 29 customers. When I run this, I should still have 29 customers. Ready, and go. Again, just make sure. Still got 29 customers.
Let's check my contacts now. All the contacts older than 2022 are gone, except for customers 3 and 26. Let's make sure those are active customers. Customer 3 is active, and I'll bet 26 is active as well. Who's active? It's getting late. And he's active as well.
I deleted all the contacts that are older than January 2022 for customers that are inactive. That's the tricky part. Doing delete queries with two tables.
If you want to learn more about delete queries, Access Expert Level 16. Level 15 starts with the action queries. In 15, we do append queries and some update queries. We start delete queries in 15. We do the simple delete queries, but in 16 is where I spend a lot more time on delete queries with multiple tables and stuff. Referential integrity, all that kind of jazz.
Of course, my lessons are designed to be followed one after the other. The TechHelp videos jump around. This one might be delete queries. The next one might be something with form properties. Whatever the questions that I get and the topics that I feel like covering that day. It's not a nice structured course where you learn this, then this, then that in the order that you're supposed to learn them in. So it's not all confusing and jumbled.
That's what my course is for. You start with the beginner stuff and you go into the expert class, level 1, 2, 3, 4, and eventually you know more than me. You're coding in VB and you're writing an app that solves world peace.
So don't forget, back up your data. Right now, do it. I've done it already. That is your fast tip for today. I hope you learned something. I'll see you again real soon.
How do you become a member? Click on the Join button below the video. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks. Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Silver members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use.
You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free expert class each month after you've finished the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, plus all of my full beginner courses for every subject, and one free developer class each month after you've finished the expert classes. These are the full-length courses found on my website, not just for Access, too. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond Sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you're a sponsor. You'll get a shout out in the video and a link to your website or product in the text below the video and on my website.
But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free.Quiz Q1. What is the main risk associated with running a delete query in Microsoft Access?
A. It can permanently erase data from your table
B. It might duplicate all records
C. It sends your database to the recycle bin
D. It makes your database slower
Q2. What is recommended before running a delete query in your Access database?
A. Compact and repair the database
B. Back up your data
C. Close all tables
D. Rename your tables
Q3. According to the video, what should you do with data that has business meaning instead of deleting it?
A. Hide it
B. Archive it, mark it inactive, or save it
C. Export it to Excel
D. Move it to another table before deleting
Q4. In what scenario does the video suggest it is acceptable to use a delete query?
A. When removing business records for inactive customers
B. When deleting temporary or unimportant data, like old log files or temporary tables
C. When updating product prices
D. When renaming tables
Q5. What is the recommended way to specify which records to delete using a delete query?
A. Use query criteria to filter records
B. Manually select and highlight records
C. Use the Compact feature
D. Change the table schema
Q6. Why does Richard recommend using ISO dates in criteria for delete queries?
A. Dates display with colors
B. ISO dates are readable and understandable globally (YYYY-MM-DD)
C. They are shorter to type
D. Access requires ISO dates
Q7. What is the difference between starting a select query and a delete query in Access?
A. They start differently with different buttons
B. They start the same but you change the query type to Delete later
C. Delete queries need to be started from VBA
D. Only select queries use criteria
Q8. What happens if you delete records in Access and want to get them back later?
A. You can use the Undo feature to restore them
B. You must restore from a backup
C. Access has a recycle bin for deleted records
D. You can ask Microsoft Support to recover them
Q9. When building a delete query involving two tables (e.g., deleting contacts for inactive customers), what must you be careful to do?
A. Delete from both tables simultaneously
B. Specify which table you want to delete records from using the star (*) of the correct table
C. Use criteria only from one table
D. Only show active table relationships
Q10. What should you verify after running a delete query that targets records from related tables?
A. The table names have changed
B. The number of records in the table is as expected and only the intended records were deleted
C. The database has been compacted
D. The report layouts still match table structure
Q11. What can happen if you bring down the star (*) from the wrong table into the query design grid when running a delete query?
A. The query will not run
B. The records from the wrong table could be deleted
C. Access will automatically fix the error
D. No records will be deleted
Q12. If you want to delete records based on conditions from two related tables, what should you do?
A. Specify the criteria for both tables and ensure you are deleting from the correct table
B. Only use a single table in the query
C. Use VBA instead of delete queries
D. Compact the database first
Q13. What levels of membership offer access to all sample databases and code vault in AccessLearningZone.com?
A. Only Free members
B. Silver members and up
C. Gold members only
D. Bronze members and up
Q14. What is the main teaching difference between TechHelp videos and the Access Expert Level courses?
A. TechHelp videos follow a strict curriculum
B. Access Expert courses follow a structured, progressive order, while TechHelp videos jump around topics
C. TechHelp videos are only for beginners
D. There is no real difference
Answers: 1-A; 2-B; 3-B; 4-B; 5-A; 6-B; 7-B; 8-B; 9-B; 10-B; 11-B; 12-A; 13-B; 14-B
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 covers how to delete records from your table using a delete query in Microsoft Access.
Among all the action queries in Access – make table, append, and update – the delete query is by far the most dangerous, since it can completely erase your data. So before you do anything else, make sure to back up your database. You should really be backing up your database every night, but it's especially important to make a backup anytime you're about to perform any significant changes. It can be as simple as copying the database file.
If you are not familiar with making proper backups, I have a free video all about this that you can watch on my website or YouTube channel. I cannot stress this enough: please back up your data before running a delete query. I don't want anyone telling me that they followed these steps, lost data, and now have no way to get it back. Seriously, if you haven't backed up your database in a while, stop and take care of that now.
Personally, I recommend never deleting any data that has real business significance, such as customers, orders, products, quotations, or anything important for your records. Instead of deleting, archive your data, save it, or just mark it as inactive. If you want to hear more about this philosophy, I have another video where I explain it in greater depth.
Before you begin, it's also important to understand how query criteria work in Access. For delete queries, you'll need to specify criteria so only the records you want to remove will be affected. If you're unfamiliar with criteria, I have a video explaining that topic as well.
With that out of the way, there are certain cases where deleting data does make sense. For example, if you have a temporary table that collects email addresses for a mail-out, you may want to clear it when you're finished. The same goes for old log data from a website or an audit trail you only need to keep for a limited period. These are examples of data that does not have ongoing business value.
So, how does a delete query work? It's similar to other action queries in Access; just like select queries are used to view records, append queries to add records, and update queries to modify them, delete queries are used to remove records from a table.
For my demonstration, I'll use two tables: a customer table and a contact table that logs every interaction with each customer. Suppose I want to delete all contacts before the year 2022. While you could manually select and delete a small set of records, with a much larger dataset or no specific order, a delete query is the ideal solution.
To create a delete query, start by creating a query in Design View, bringing in the relevant table. Set your criteria, such as selecting contact records where the date is earlier than January 1, 2022. I use the ISO date format (year-month-day) for clarity, especially with international students. If you are interested in more about ISO dates, I have a video on that topic too.
After running the query as a select query first, you can check which records are about to be deleted. Once you're sure, turn it into a delete query. When you run it, Access will prompt a warning unless you have disabled system warnings in your settings. I typically turn those off because they tend to interrupt the workflow. If warnings are enabled, Access will tell you how many records are about to be deleted, and you'll need to confirm.
Once executed, those records are gone. If you need them back, your only option is to restore from your backup. This is another reminder of why backup is crucial.
For demonstration, I restore my database using the backup to run through another example, ensuring all records are in place again. This process highlights how easily data can be recovered if you have a backup.
Delete queries can become more complex when you want to delete records based on criteria that involve more than one table. For instance, you might need to delete contacts occurring before 2022, but only for customers who are inactive. In this situation, you would set up a query that includes both the customer and contact tables. Establish a join between them and set up criteria such that only contacts associated with inactive customers and before 2022 are flagged for deletion.
When your delete query involves multiple tables, Access will require you to specify the table from which records should actually be deleted. This step is critical – be sure to select the correct table, or you risk deleting the wrong data. In our case, you should only delete from the contact table. After confirming your settings, you can run the query and verify that only contacts for inactive customers before 2022 have been removed, while all customers remain.
If you want to master delete queries, I recommend my Access Expert Level 16 class, which covers these more advanced scenarios using multiple tables and addresses issues such as referential integrity. The basics of action queries are introduced in Level 15, where we cover append and update queries; then, simple delete queries are introduced, and more advanced techniques are covered in Level 16.
Keep in mind that my TechHelp videos tackle specific questions in no particular order, which can seem a bit disorganized if you're looking to build a structured foundation. My full courses take you step-by-step, starting with the beginner level and advancing all the way up to expert skills, helping you develop a comprehensive understanding of Access.
Remember, never skip the backup step. I cannot emphasize this enough.
If you want information about joining and accessing more content, just look for the Join button under my video where you'll see all the different membership levels. Silver members and above receive extended TechHelp videos, free beginner classes each month, access to downloadable sample databases, the code vault, and higher priority for TechHelp questions, plus a free expert class every month after you finish the beginner series. Platinum members unlock all those perks plus higher priority on questions, all beginner courses, and a free developer class each month after you complete the expert series. I also teach courses on Word, Excel, Visual Basic, and more.
Even so, these TechHelp videos remain free, and I plan to keep producing them as long as they're helpful.
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 Deleting records with a delete query in Access
Backing up your Access database before deleting
Understanding when to archive instead of delete
Selecting records to delete using query criteria
Using ISO dates in Access queries
Creating a delete query from Query Design
Specifying delete query criteria with date ranges
Running a delete query and verifying deletion
Restoring data from a database backup
Deleting records based on conditions in two tables
Joining tables in a delete query
Setting delete conditions for related tables
Using the star (*) to specify delete table
Avoiding accidental deletion from the wrong table
Testing and reviewing results after delete queries |
