17. Delete Query (Programming In Microsoft Access 2013) 🎓

hello again everyone and welcome back to programming in Access 2013 my name is Steve Bishop and today we're going to be wrapping up the series on queries by going through a delete query so I'm going to hop out of here go back over to access our database here and I've got the table one customers table open and you'll notice that I do have new IDs for those four companies that were added I did a little bit of work in the background here and had to re-add them so they've got new ID values in here so just in case you were wondering but let's say that I want to delete all four of these customers from my list okay and the way I would do that is I would run a delete query but before I get into that you may be wondering well what if I just want to delete a single piece of data from that particular table what if I want to keep the ID but I just want to remove what's in the customer name well unfortunately you're not going to want to do that in a delete query you're going to want to do that with an update query because in an update query if I wanted to change hamster wheels Inc here to nothing I could actually use the update and I could say update two and then say blank just by using double quotation marks that essentially means a blank string okay so I'm not going to get into that if you if you want to learn more about how to do that you can all be it you can you know do some Google research if you want it's not really all that hard if you really think about it you just do an update query and you change the value to blank instead of something else okay all right now so I want to delete these four rows of data and I know that the ID is 20 through 23 I can use the fact that this is a number to my advantage okay so what I'm going to do is I'm going to create a new query go to the query designer and in the past you may have noticed I would exit out of this dialog box then click on the delete button I can actually go ahead and select the table on customers here right from this dialog and go ahead and add it and close out of here that's perfectly fine and then I can switch over to the delete to make it a delete query that's frequently okay all right now when you're doing a delete like I said you're going to be deleting entire rows of information so since that's what you're going to be doing if we're only dealing with one table all I need to do is specify that the the criteria in which I'm going to be doing my evaluation of which rows to delete and in here I need the ID field because again remember we're going to say all of the IDS that are greater than 19 so basically all the idea is IDs 20 through 23 okay so what I can do here then is I can say all of the IDS in table one customers where it is greater than 19 and less than 24 all right now that's going to do exactly what it is that we're looking to do it's going to delete everything 20 through 23 I could also use the greater than or equal to 20 that's perfectly fine and less than or equal to 23 that would do exactly the same thing these are regular mathematical expressions that you would find and the greater sign is obviously means any value any ID value that is greater than 20 or in our case we want it to be greater than 19 and any value that is less than 24 okay so that's how you do that now you may notice that I have this end keyword here and it's perfectly fine to have this end keyword in your criteria in this particular example but it is perfectly acceptable to do it this way as well and some people prefer to have the organization set up this way okay notice that now I'm essentially doing the same thing I'm taking the ID field and I'm saying all of the IDS greater than 19 and we're ID is less than 24 this is doing exactly the same thing it's just that I now have two columns involved in my query you can really do it either way it's entirely up to you it's entirely up to the developer how they want to arrange it I've done it both ways it doesn't make a lick of difference really okay so I want to go ahead and before I run this I'm just going to throw the custom name field in here I'm not going to do any sort of criteria on it because all I want to do is when I go to preview this I want to see what the customer names are so I'm going to preview it here and I see ID 20 hamster wheels Inc ID 21 hyphenated Corp 22 et cetera you can see the whole list of the four companies that I would be deleting are listed here just as if I was doing a select query all right so let's go back and now that I know that that's definitely the information I want to delete I'm going to go ahead and just click run says I'm about to delete four rows from the specified table all right sure I'm going to go ahead and click yes and just like that folks we now have those fields deleted and even gives us since I already had the table open it even tells us hey this information has been deleted it's not there anymore and if I want to get rid of that and I just close out of the table and reopen it and voila those fields are now gone but let's get a little bit more in depth let's let's say that hey you know what there's this one customer here hyphenated Corp which I still don't have an address for right because customer ID number five is missing from our list there's no information in our table and addresses and let's say it's a couple years down the road we still haven't heard back from them and we still don't have an address form and this is really getting to the point where it's ridiculous to even hold that data anymore why should we do that so I'm going to go ahead and delete this bit of criteria first and I'm going to go ahead and add the table one addresses list here and notice we get our relationship here where we're saying all of the customers and give us also all the addresses that are equal and wherever you know wherever there might be no address for a customer it's going to be blank remember that just like in our Select query so here's what I'm going to do I first need to specify which table am I going to be deleting information from okay I remember this is a delete query the way you do that is you go ahead and select the asterisk first and put that in as your first field and you'll notice that you get this delete from okay that means that you want to specify this is the table that I will be deleting from and then what I'm going to do is I'm going to go over here to the customer ID and I'm going to double click and now you can see the delete says where which basically is saying I'm using the criteria I'm using a wear criteria where something is something in the customer ID and I'm going to use the term is oops is null and null is basically saying absent of data it doesn't mean a blanks you know a blank string it doesn't mean nothing is there it means literally no data it's bereft of any information there's nothing there okay so I'm going to say is null so what I'm essentially saying here is wherever the relationships is that I have a customer in my customer list but we don't have a customer ID for that customer listed in the table one addresses I want to delete from the table one customers that's a pretty complex situation a pretty complex scenario of what it is that I want to do I hope you've caught on I hope that makes sense to you but this is a really key and crucial piece of information of something you're going to need to know how to do because targeting your data and making sure you're deleting the right information is very very important so I'm going to go and click on view and we can see there's hyphenated Corp that's the only customer that I have in my list that I do not have an address for all right now I'm going to go back here and if I click run I'm going to get an error here it says could not delete from specified tables okay and that's because of a silly little option here that we've got called unique records I got to change this over here - yes and that should do it so I've got my unique records here set - yes okay this is just a little flag that Microsoft has put in to make sure that I'm aware that I'm I've got unique records in here I'm really not entirely sure what that's all about if you wanted to find out what that particular flag is for remember you can go ahead and have the cursor in there and then press f1 and we can get ourselves over to the windows help which will give us unique records property so all right I'm going to go ahead and close that I guess has something to do with duplicate records anyway okay so we know this is a unique we know this is a unique query now if I go ahead and hit run says I'm about to delete one row from the specified table I click yes and now if I go in here you can see that deleted is there it's removed that customer from our list all right I hope that's been informative to you I hope you've learned something here as always you can subscribe to my channel here send me a message if you if you have any questions and don't forget to like thank you very much

Channel: Programming Made EZ

Views: 96,862

Rating: 4.9553075 out of 5

Keywords: Microsoft, Access, 2013, Programming, VBA, Visual Basic, Applications, tutorial, lesson, guide, database, SQL, beginner, advanced, software, microsoft access, table, query, form, code, coding, development

Id: Tra-rtxcHVI

Length: 10min 11sec (611 seconds)

Published: Tue Dec 24 2013