When working on a database such as those that can be developed in Microsoft Access it is very common that they end up managing a great deal of data, so it can be quite tedious or complicated having to delete a large amount of data why They have been badly or simply because you no longer need them.
To carry out these types of usual activities you can make use of what they are deletion queries, which will allow you quickly delete a lot of data or a data set. This is because this type of tool allows the user to specify criteria that allow them find and delete data needed quickly.
All this without a doubt can be translated into a time savings and better performance when working on this program. In addition, each of the applied queries you can use them again as long as it has been stored. That is why we are going to teach you below how to create a quick and easy delete query in Microsoft Access.
What is a delete query and what is it for in Access?
The deletion queries are those queries that are responsible for eliminating a data table everyone the records that meet a search criteria that has been previously specified. That is, this type of function allows the data deletion massively in just a few seconds.
Therefore, this tool is recommended to be used every time you want delete different records quickly. In this way, it is recommended to use it especially when you are working with many data and it only takes remove some of them, since here you can set the criteria necessary for it to be carried out the search and later the elimination of all the corresponding data.
Also, deletion queries have been created with the aim of helping users to save time when working in Access, this is how each of the applied queries They may be saved to be reused later if necessary. Now in case you just want delete one or two records, you don't necessarily have to apply this tool.
Please note that these inquiries are applied for the purpose of save time and labor when having to dispose of a large amount of data or data sets. Finally, it is important to mention that before get rid of a lot of data it is recommended to apply a backup that allows you retrieve information in case something goes wrong.
What are the advantages of using delete queries in a database?
As has been mentioned in the post, deletion queries undoubtedly offers a series of advantages to users at the time of having to work with a lot of data in a database.
That is why here we are going to show you the main advantages that you can obtain when applying these types of tools:
- It allows you to carry out deleting a lot of data through a set of criteria that has been previously specified.
- To the delete a lot of information simultaneously can save time and increase your performance at the time of work in Access.
- It is recommended to use it only when you want undo many records.
- Each of the applied queries They may be stored to be again applied later in case they are necessary.
- It becomes an ideal tool for when you are working with a fairly large database.
- Let deleting a lot of data be much easier than having to do it manually.
Learn step by step how to create a delete query in an Access database
It is important to mention that this process can be done either through a delete query or an update query, This means that the first thing to do is to know what type of query should be applied. In the case of deletion queries apply when needed remove entire records in the rows of a table or two tables that are related at the same time.
While update queries It allows remove individual field values from a table. Therefore, depending on these criteria you must choose which of the two should be used. If you apply the delete query then how results you will get the elimination of all the data of each field and with the update query will facilitate removing values when updating existing values to null.
Before performing a query to delete data from a data table in Access, it is essential to perform the following checks:
- The file being used must not be read only. In order to check this you must right click on "Start" and there you select the option to start the "Windows Explorer". Then you must right click on the file to work and in drop down menu Choose "Properties", there you can check if the option "Read only" is activated, if so you must deactivate it immediately.
- You should also make sure you have all the necessary permits to delete the records from the database.
- You must ensure that the content of the database is enabled. As usual, Access by default usually performs a lock on all action queries, that is, those that allow delete, update and create a table.
- Make sure to ask other users of the database than completely close all forms, queries, tables and reports than use the data to be deleted. This will avoid generating a blocking for infringement.
- Finally, before performing removal of records It is advisable run a database backup in case you want to revert the changes you have made.
If in your case a great number of users connects to the database, then you need to open it in exclusive mode and not normal.
Let's see below:
Open the database in exclusive mode
- In this case you must click on the tab "Archive" and then in "Open".
- Now you must find the database file with which you want to work and once found and selected you must click on the option "Open in exclusive mode".
Back up the database
As mentioned above, it is very important to be able to carry out a backup before data deletion, as this will give you a backup in case something goes wrong.
To do this, follow these steps:
- The first thing in this case is to go to the "Archive" and there select the option "Save as".
- Now you must click on the option "Save database as" and then select the item of "Database Backup". Automatically Access will close the original file and it will create a backup and reopen the original file.
- Now you must click "Save as" and there set the name and location that you want to give to the backup and finally select the button "Save".
Use the delete query
In order to make use of a delete query eYou need to go to the tab "Create" and then to the group of "Queries" after that you must go to "Design of the query". In the box "Show table" you must double click on each of the tables where you want delete records and finally select the button "To close".
Then the table will appear as a window in the upper section of the query design grid. Here you must click on the * so you can start add all the fields from the table to the layout grid.
Use specific criteria for the delete query
If you want use a criterion to apply that query, then you must double click on the field you want use as criteria for removal. Now you must write one of the criteria in the query designer criteria row and then deactivate the checkbox "Show" of each criterion field.
For this, it will be necessary to carry out these steps:
- In the design tab you must click on "View" and subsequently "Datasheet view".
- Now you must check that the query has returned records want to delete and press the key combination "Ctrl + G" for the changes to be saved to the query.
- Finally, to execute the query you must double click on the query in the navigation pane.
Use an update query
If according to your needs you have to use a update query, then this procedure will run for removal of individual fields in the data table.
To do this, you must follow these steps:
- To start you must click on the tab "Create" and then in the query group, and there choose the design of the query you want.
- Now you must select the table that contains the data you want to delete, In the case that the table is related, then you must choose the table on side one of the relationship and click on "Add" and then in "To close".
- Here you must double click on the asterisk * to add all the fields from the table to the layout grid. In this case you can add all the table fields that allow the delete query. You can optionally specify criteria for one or more fields in the designer criteria row and then uncheck the box "To show".
- In the group "Results" located in the tab "Design" you must select the option "See" and then "Datasheet view", here you should check that the query returns the records you want to delete.
- Finally to execute the query you must select the query in the navigation pane.