Logo
Mail us
ben@accessdatabasetutorial.com
accessdatabasetutorial
Home » MS Access » How To Remove Multiple Records With A Microsoft Access Delete Query

How To Remove Multiple Records With A Microsoft Access Delete Query

How To Remove Multiple Records With A Microsoft Access Delete Query


Here’s an article I wrote for EzineArticles about handling multiple records or duplicated records in a table and that by calling a couple of the four action queries; in this case the delete and append queries you can clean up your database without the need to use any form of coding using either VBA or a macro (or two).

Take a look at this article and I welcome your comments in the reply box below and share your experiences with this community.

How To Remove Multiple Records With A Microsoft Access Delete Query

Microsoft Access Delete Query – one of the four action queries.

Reporting in a Microsoft Access database is as only good as data inputting or how records are stored (including importing) in your Access database – You can only get out, what you put in!

Microsoft Access database icon - blue

If data archiving is out of your remit and your have no control, how do you handle duplicated or multiple records that can sometimes leak into your Access database?

Microsoft Access queries are the most powerful set of objects available as it serves you for your normal reporting requirements as well as maintaining data processes or data management procedures. These types of queries are known as ACTION Queries.

Why do you sometimes have multiple records in the first place?

  1. Well maybe, they are not duplicates but repetitive records for different instances and therefore should not be removed! Check your records first and the best way perhaps is to locate the unique identifier first (normally the AutoNumber field).
  2. If it’s a genuine duplicate, some relational issue has occurred between the joins of two tables (or three). Check their join types are correct.
  3. Some instances, importing data without a unique qualifier will yield many unnecessary duplicates whether stored manually or automated with either a macro or another Action Query (namely, APPEND Queries). This is why it’s useful to store a unique value within your tables.

How to remove multiple records – using the Microsoft Access Delete Query.

  1. Create a standard query and choose the fields, which will be used to test and apply the criteria for deleting data. You do not require all the fields, as the whole record will be deleted in any case.
  2. Apply criteria across one or more fields and preview the recordset (answer).
  3. Change from the Select query to the Delete query.
  4. Run the query and you should be prompted before actioning the query.
  5. Optionally, decide if you wish to save the query for next time and close the query.

Make sure you have switched on all warnings first before proceeding as you can imagine how sensitive this type of Access database delete query procedure is causing you to lose more data than you wished for. This is controlled in Microsoft Access options.

Another Tip for you! Make sure you back up your database files before executing the Access delete query as you will be unlikely to reverse this action once it completes the task. You could always use another action query called Make-Table.

I invite you to keep up to date with my articles and eBooks (More Access Database queries) which covers a lot of details and can be found at https://AccessDatabaseTutorial.com.

From Ben Beitler – “Your Access Database Expert”

Article Source: http://ezinearticles.com/expert/Ben_S_Beitler/840635

How To Remove Multiple Records With A Microsoft Access Delete Query

This has proved useful when needed to clean data that was either imported into MS Access or a badly designed database that was inherited without having to change the structure that would have caused an adverse effect in the general day to day running of other Access objects and avoid that unwanted task of redesigning the whole database.

Don’t forget to share your experiences below, I would love to hear from fellow database users!

Tags: , , , , ,

2 Responses so far.

  1. Hi,

    I have a mailing list with multiple subscibers received through double optin email processes on 8 different websites. Many of the subscribers have opted into lists from 2 or more of these websites. I have run a duplicates query and removed all duplication.

    I have the following fields in the databaseL ID, Name, Email, and LeadSource

    I am constantly finding that some emails are undeliverable to certain older email addresses and wish to remove those records from the database. What I have is a singular list of email addresses to be removed.

    I realize I can manually find these addresses and delete them one at a time. However, is there some way to create a query that will find them and delete the entire record (row) in which they reside without having to run the query repeatedly to get rid of them which would be almost the same as doing it manually. The Email field is the 3rd field in the database.

    If this is possible, I would appreciate the syntax for creating such a query if you are not too busy.

    Thanks!

    • Ben says:

      Hi,

      There is a query called DELETE Query as you have identified.
      However, unless there is a patter to removing delete items, then you are left with one of the following approaches:
      1. Manually deleting from a table (as you will probably know how to do).
      2. Having a form which lists all records but focusing on the email field in a List box to allow multiple selection before deleting as an action.
      3. Using VBA code with the above or using code to try an find a patter (if one) and/or using a form interface to make navigation and collecting unwanted emails before deleting.

      You can also add an new field called ‘Mark As Deleted’ and tick those that are redundant and then create a DELETE query using this marked field as the criteria.

      Hope this helped.

Leave a Reply

Your email address will not be published. Required fields are marked *