Microsoft access delete query

Microsoft access delete query DEFAULT

Microsoft Access Delete Query, SQL Syntax, Examples and Errors

Do You have Rights to Delete Records?

If a DELETE Query fails to delete records, the first thing to verify is that the underlying table is updateable. Simply open the table and manually try to edit a field or delete a record you expect to eliminate. If you can't do it manually, the query can't make the deletions either. This can be due to several reasons:

  • The database file is set to Read-only. None of the data can be modified. Change this at the Windows level. If the database is on a CD, copy it to your hard disk.
  • If you're using Access workgroup security, you may not have rights to delete the data. In this case, login as the administrator or with a user name and password that gives you the appropriate rights.
  • If the tables is linked, you may not have rights to modify the data with the backend data source.
  • If you are in an Access Data Project (ADP) or linked to a SQL Server table and the table doesn't have a primary key, you can't modify the table's records from Access.

Query Fails with This Message: "Could not delete from the specified tables"

Assuming you can delete records from your table, your query may fail and display a "Could not delete from the specified tables" error message when you run it:

Could not delete from the specified tables error with Delete Query
Delete Query Error: Could not delete from the specified tables

This error appears when the table is linked to another table's fields, and the linked field(s) is not the primary key. Access interprets the link as not representing a one-to-one relationship, and prevents deletions.

NOTE: In VBA, the query does not trigger an error or display this message. It simply fails to run.


Example of a Delete Query that Fails to Run

For instance, you may want to delete people from TableA who are in TableB by linking their name fields (both tables have another field as its primary key):

DELETE TableA.* FROM TableA INNER JOIN TableB ON TableA.Name = TableB.Name

Unfortunately, the query triggers the warning message when you try to run it.


Use DISTINCTROW with Delete Queries for Non-Key Field Joins

For the Delete Query to work, Microsoft Access requires the SQL syntax to include the DISTINCTROW syntax to specify that it's a unique query relationship between the two tables:

DELETE DISTINCTROW TableA.* FROM TableA INNER JOIN TableB ON TableA.Name = TableB.Name

This setting can also be set from the query's Property Sheet when editing the query in Design View. Simply set the Unique Records property to Yes:

Unique Record Property for Delete Queries to add DistinctRow to SQL

By doing so, the DISTINCTROW term is added to your DELETE query's SQL statement.

Sours: https://www.fmsinc.com/microsoftaccess/query/snytax/delete-query.html

Creating a Delete Query in Microsoft Access:

A Microsoft Access delete query deletes records from a single database table or database tables. Of all of the different action queries available in Microsoft Access (Append Queries, Update Queries, Make-Table Queries and Delete Queries) the delete query is one of the most dangerous. Unlike the others mentioned, the Microsoft Access delete query will remove records from your tables permanently and forever.

As with the other types of action queries, the delete query will work with a group of records that meet a specified criteria that you apply. You can use the delete query to remove all records or only records that meet the defined criteria.

If you wish to use the delete query to remove records from multiple tables rather than just a single database table, you will need to ensure that:

  • You have relationships defined between related tables in your Microsoft Access database
  • You have enforced the Referential Integrity for the relationships between your chosen tables.
  • You have opted to Cascade Delete Related Records for the relationship type

If you are using the delete query to remove records from multiple tables that are related in a One-To-Many relationship without having defined the option to Cascade Delete Related records, Microsoft Access will only delete the records from one table at a time. If this is the case, you must delete the records from the many side of the relationship first (to ensure against orphaning records), and then delete the records from the one side of the relationship.

Note: Due to the permanent effects of working with a delete action query, you should always make a backup copy of your tables, or your database before attempting this option.

How to create a Microsoft Access Delete Query:

  1. Create a SELECT query to determine the records that will be deleted. Apply any required query criteria
  2. In the query design view, click on the drop-down arrow to the right of the Query Type button and choose Delete Query
  3. If needed, modify the query further so that the correct fields will be deleted with the desired new data
  4. Click on the Run (!) button to run the action query
  5. When informed of the number of records to be deleted in the Microsoft Access dialog box, click Yes
  6. Close the query, saving if required

Now consider the following scenario example, that will illustrate the use of a Microsoft Access delete query:

Your company runs training courses, with students signing up for these courses. Student membership lasts for 2 years. All of this information relating to their details is stored in your StudentInformation table. You would like to remove any records from this table that are not of current students, so you will archive records by initially appending the data from your StudentInformation table into another table (ExpiredStudents) and then deleting these records from the StudentInformation table.

If you have read the previous article that detailed How to Append records using a Microsoft Access Append Query you should be aware of the following:

The original StudentInformation table, containing 52 records, would look like the following:

The original Student Information table, containing 52 student records

The original Student Information table, containing 52 student records

As you will see, the above table contains contact information relating to the student members. You will see from the fields included, that there is a field detailing when the Student Enrolled on the course (dtmEnrolled). This field also is displaying records older than two years old.

In our scenario, we only wish to keep records in the table where the student is still currently active. With the courses being a two year duration, we only want records that are greater than or equal to today's date minus two years.

To allow us to append records to an archive table, we must first have the archive table created, whether it be in the active database or in another database. The table should ideally have exactly the same structure as the original table and contain the same field names and data type/sizes.

In our example we have an archived table created, named tblExpiredStudents, which will hold the historical records of students whose two years have expired.

After having appended these records to the archive table, named tblExpiredStudents as detailed in the How To Append records using a Microsoft Access Append Query we now need to create a Delete Query to remove these records from the original table named tblStudentInformation:

The initial design of the query, prior to changing to the append query type

You will see that we have added a criteria to the dtmEnrolled field, as specified we want to delete any records that are older than two years from today's date. The criteria that is applied is:

<=DateAdd("yyyy",-2,Date())

This will display only records that are older than 2 years from today's date.

Before we change the query type to an Delete query, we should check the results that this displays. We can do this by clicking on the Datasheet button Datasheet button on the toolbar.

If we check the datasheet displaying the results of running the query with the DateAdd criteria applied, we will now see that the record count is displaying only 20 records. This is showing 20 records that need to be deleted due to them being greater than 2 years old:

The query datasheet results, with the DateAdd criteria applied.

As we have now copied (appended) the data to the archive table, we can now remove (delete) the data from the main table. To do this we need to go back into the Microsoft Access query design view.

Once in design view, we change the query type using the Query Type button The Query Type button on the toolbar. From the query type drop-down list, change the query type to a Delete Query.

You will now see the the query design includes a new row labeled Delete, which allows you to specify a criteria to which the data will be deleted. You should ensure that you specify a WHERE condition here, or you will end up deleting all records from the table. You will see below this row in the query design, that includes the WHERE criteria which will delete all records older than 2 years:

The delete query design, showing the WHERE condition row in the design grid.

We now need to Run this query, using the Run button The Run button to run the action query to delete the data from the original database table that matches the applied DateAdd criteria. The warning dialog box indicates the number of records that will be deleted, click Yes to accept this:

The warning message, warning that you are going to delete records from the original database table.

This will now have deleted all records that matched the applied criteria from the original database table.

Remember that the delete query will permanently remove records from the specified table(s), therefore it is very important that you have either appended your data to an archive table, or have backed up the table(s) or database.

Check Your Delete Query Results

Once you have ran the delete query, you can check the results by once again viewing the datasheet results. If the delete action has ran correctly, there will now be no records visible in the datasheet.

Sours: http://www.databasedev.co.uk/delete_query.html
  1. 1955 chevy truck diecast
  2. Albino dwarf hamster
  3. Oneplus unlock token

DELETE statement (Microsoft Access SQL)

  • 2 minutes to read

Applies to: Access , Office

Creates a delete query that removes records from one or more of the tables listed in the FROM clause that satisfy the WHERE clause.

Syntax

DELETE [table.*] FROM table WHERE criteria

The DELETE statement has these parts:

table

The optional name of the table from which records are deleted.

table

The name of the table from which records are deleted.

criteria

An expression that determines which records to delete.

DELETE is especially useful when you want to delete many records.

To drop an entire table from the database, you can use the Execute method with a DROP statement. If you delete the table, however, the structure is lost. In contrast, when you use DELETE, only the data is deleted; the table structure and all of the table properties, such as field attributes and indexes, remain intact.

You can use DELETE to remove records from tables that are in a one-to-many relationship with other tables. Cascade delete operations cause the records in tables that are on the many side of the relationship to be deleted when the corresponding record in the one side of the relationship is deleted in the query. For example, in the relationship between the Customers and Orders tables, the Customers table is on the one side and the Orders table is on the many side of the relationship. Deleting a record from Customers results in the corresponding Orders records being deleted if the cascade delete option is specified.

A delete query deletes entire records, not just data in specific fields. If you want to delete values in a specific field, create an update query that changes the values to Null.

Important

  • After you remove records using a delete query, you cannot undo the operation. If you want to know which records were deleted, first examine the results of a select query that uses the same criteria, and then run the delete query.
  • Maintain backup copies of your data at all times. If you delete the wrong records, you can retrieve them from your backup copies.

Example

This example deletes all records for employees whose title is Trainee. When the FROM clause includes only one table, you do not have to list the table name in the DELETE statement.

Sours: https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/delete-statement-microsoft-access-sql

Delete Query Tutorial

The Delete query in MS Access SQL command used to remove one or more rows of data from a relational database table.  The number of rows deleted is dependent upon the existence and criteria within the where clause of the delete query.  The basic syntax of the Access Delete query is:

Delete Table Name.* From Table Name Where some criteria;

Note that using an Access delete query without the where clause will delete all rows from the specified table.

The asterisk in Table Name.* is a wildcard character and refers to all fields within the table.  You may only delete entire records rather than values in particular fields therefore the asterisk is mandatory.

If Table Name is a table which is defined as the master of a master-detail relationship and the cascade delete property of the relationship is set to true then corresponding records in the detail table will also be deleted.

The delete method is often preferable to the drop table method, which completely removes the table from the database, because all of the characteristics of thetable remain intact &#; such as the indexes, relationships and primary keys.

Now with real column and table names:

Delete *  From M_Employees Where  Emp_Name = &#;Joe&#;;

The above query will delete all employees with an employee name of Joe.

Microsoft Office:
MS Access Through Access & Office

>>>>>NEW LINKS WILL BE HERE AFTER PAGES COMPLETED<<<<<< Open Email Using SendObjectAccess Download User Inactivity LogoutVBA Code Disable Shift KeyDemo Send EmailMicrosoft Access Tutorial Download (Advanced) Sequential Counter in QueryMulti Select List Box Query ParametersRow Level Data SecurityProgramming MS AccessSecurity AlternativeSingle User Inventory CalculationsDownload How To PerformMulti-User Inventory CalculationsMake Dependent Combo Box CodeMS Access Tutorial Union Query Example (Simple)Union Query (Advanced)Access Tutorial How ToFill Fields From Combo BoxUse Global Variables as ParametersContinuous Form Dependent Combo BoxHow To Program Continuous-Continuous Master/Detail FormsAccess Bar Chart / Bar Graph Programming Crosstab Query ExampleTransferText & OutputTo Microsoft AccessDoCmd.OpenForm & OpenArgs VBA ExampleRunning Sum Query MethodChoose Command Dynamic SQL Order ByAccess Conditional FormatAccess Report Banding

Popup Form Control Method

Microsoft Office VBA, MS Access , , , ,

Sours: https://blueclawdb.com/access-database/tutorials/sql-delete-query/

Query delete microsoft access

How to Run a Query in Microsoft Access

By Mahesh Makvana

ShareTweetEmail

Want to retrieve or update certain records in your Microsoft Access database? Queries in Access can help you do that.

Want to retrieve or update certain records in your Microsoft Access database? Queries in Access can help you do that. Queries let you retrieve, update, and delete records in your tables using custom criteria.

You can define these custom criteria yourself. When the records in your tables match your criteria, the action specified in your query is run.

There are multiple query types that you can use in Microsoft Access. Here we show how you can create those queries in Access.

1. How to Run a Select Query in Microsoft Access

As the name suggests, a Select query in Access lets you select and retrieve certain records from your tables. You can specify a custom condition and Access will retrieve only the records matching this condition.

Related: How To Write Microsoft Access SQL Queries From Scratch

Before you create a query, you must have a table in your database. Once you’ve created and populated a table with some data, you can run a query as follows:

  1. Open your database in Access, click the Create tab at the top, and select Query Wizard.
  2. Choose Simple Query Wizard and click OK.
  3. Select your database table from the dropdown menu. Then, select the field that you’d like to use in your query and click the right-arrow icon. You need to do this for each field that you want to add to the query.
  4. If you want to add all the fields, click the double-right-arrow icon. Then, hit Next.
  5. Select the Detail option and hit Next at the bottom.
  6. Enter a name for your query, select the Modify the query design option, and click Finish.
  7. You can now specify custom criteria to filter records in your table. To do this, put your cursor in the Criteria field for your column, type your criteria, and press the save icon at the top-left corner.
  8. As an example, we’ll configure the query to only show users that are younger than 35 years. We’ll type <35 in the Criteria field for the Age column.
  9. Double-click on your query in the Navigation Pane and you’ll see the filtered records.

2. How to Run an Update Query in Microsoft Access

An Update query looks much like a Select query but this one edits your table records. This query modifies and updates the records in your tables using your custom criteria.

For example, if you want to change the country for all your users in Germany to the US, you can create a custom criterion that automatically finds the required records and updates them for you.

Here’s how you create an Update query in Access:

  1. In Access, click the Create tab and select Query Wizard.
  2. Follow the on-screen instructions to add tables and fields that you want to use in your query.
  3. When your query opens in Design View, click Update from the Query Type section at the top. This will convert your Select query to an Update query.
  4. Click the Criteria row for the column that you want to update. Then, enter custom criteria to filter your records. We’ll use =Germany as we want to find all users that have Germany as their country in the table.
  5. Type what you want to replace the original record with in the Update To field. We’ll enter US as we want to update all records from Germany to the US.
  6. Press Ctrl + Save to save the query.
  7. Double-click on your query in the Navigation Pane to run it.
  8. You’ll get a prompt that says the query will make changes to your tables. Click Yes to continue.
  9. Another prompt will display the number of rows that will be affected. Click Yes to continue.
  10. Open your table and you’ll find that the query has updated the records as specified.

3. How to Run a Delete Query in Microsoft Access

If you want to remove certain records from your tables, a Delete query can help you do that. This query deletes the rows from your tables that match the given criteria. You’re free to use any criteria of your choice.

Keep in mind that you can’t restore the records once they are deleted. Therefore, keep a backup of your database before running a Delete query. If you aren't sure how to backup something, it's worth reading the Microsoft support article on the backup and restore process.

Here’s how you create the query:

  1. Click the Create tab and select Query Wizard in Access.
  2. Follow the usual steps to make a query.
  3. Once the query is created and is open in Design View, select Delete from the Query Type section at the top.
  4. Enter the criteria to delete records in the Criteria field. As an example, we’ll delete the records where the Age column is less than 40. For that, we’ll type <40 in the Criteria field.
  5. Press Ctrl + Save to save the query.
  6. Run the query by double-clicking on it in the Navigation Pane.
  7. Click Yes in both the prompts that appear on your screen.
  8. The matching records will be removed from your table. You can verify this by opening the table.

4. How to Run a Make Table Query in Microsoft Access

A Make Table query creates a new table from the filtered data of your existing tables. If you have several tables and you want to retrieve certain records from those tables and create a new table, this is the query you can use.

You can use this query with a single table database as well.

Here’s how to make a Make Table query in Access:

  1. Click the Create tab, select Query Wizard, and follow the instructions to create a basic query.
  2. On the query Design View screen, click on Make Table in the Query Type section.
  3. A box will appear asking you to enter a name for your new table. Enter a descriptive name and click OK.
  4. Put your cursor in the Criteria row for the column that you want to filter. Then, type the criteria to filter your records. We’ll create a new table holding users from Germany and so we’ll enter =Germany in the Criteria row for the Country column.
  5. Hit Ctrl + S on your keyboard to save the query.
  6. Double-click on your query to execute it.
  7. Hit Yes in the prompts on your screen.
  8. A new table will appear in the Navigation Pane. Double-click on it to view your filtered records.

Find Records in Access Without Hassle

If you have thousands of records in your tables and you’re struggling to extract certain records, queries can help you easily find the records you need and even help perform actions on them.

If you’re looking to add more data to your tables, Forms in Access is an easy way to do that. A form helps input new data into your tables by letting you focus on one entry at a time. This helps remove the possibility of accidentally modifying other records in your tables.

ShareTweetEmail

How to Create a Form in Microsoft Access

There are many ways to add data to a Microsoft Access Table. Using Forms is the easiest way to build your database.

Read Next

About The Author
Mahesh Makvana ( Articles Published)

Mahesh is a tech writer at MakeUseOf. He's been writing tech how-to guides for about 8 years now and has covered many topics. He loves to teach people how they can get the most out of their devices.

More From Mahesh Makvana

Subscribe to our newsletter

Join our newsletter for tech tips, reviews, free ebooks, and exclusive deals!

Click here to subscribe

Sours: https://www.makeuseof.com/how-to-run-a-query-in-microsoft-access/
Use a Delete Query in Access

When you want to either quickly delete a lot of data or delete a set of data on a regular basis in an Access desktop database, a delete or an update query might be useful because the queries make it possible to specify criteria to quickly find and delete the data. Using a query can also be a timesaver because you can reuse a saved query.

Note: Before you delete any data or run a delete query, make sure that you have a backup of your Access desktop database.

If you want to only delete a few records, you don’t need a query. Just open the table in Datasheet view, select the fields (columns) or records (rows) that you want to delete, and then press DELETE.

Important: The information in this article is intended for use only with desktop databases. You cannot use delete or update queries in Access web apps.

In this article

Choosing a query type

You can use either an update query or a delete query to delete data from your database. Select a query based on the details in the following table:

Type of query

When to use it

Results

Use a delete query

To remove entire records (rows) from a table or from two related tables simultaneously.

Note: If the records reside on the "one" side of a one-to-many relationship, you might need to change the relationship before you run the delete query. See the section on deleting data from related tables.

Delete queries remove all the data in each field, including the key value that makes a record unique

Use an update query

To delete individual field values from a table.

Makes it easier to delete values by updating the existing values to either a null value (that is, no data) or a zero-length string (a pair of double quotation marks with no space between them).

Top of Page

Things to verify before you use a query to delete any data

  • Make sure that the file is not a read-only file:

    1. Right-click Start and click Open Windows Explorer.

    2. Right-click the database file and click Properties.

    3. See if the Read-only attribute is selected.

  • Verify that you have the necessary permissions to delete records from the database. If you are not sure, contact your system administrator or the database designer.

  • Make sure that you have enabled content in the database. By default, Access blocks all action queries (delete, update, and make-table queries) unless you first trust the database. For information about trusting a database, see the section Stop Disabled Mode from blocking a query.

  • Ask other users of the database to close all tables, forms, queries, and reports that use the data that you want to delete. This helps avoid lock violations.

  • Before you edit or delete records, making a backup copy of the database is a good idea in case you want to reverse your changes.

Tip: If a large number of users connect to the database, you might need to close the database and then reopen it in Exclusive mode.

To open a database in Exclusive mode

  1. Click File tab > Open.

  2. Browse to and point to select the database, click the arrow next to the Open button, and then click Open Exclusive.

    Opening a file in Exclusive mode

Back up the database

  1. Click the File tab, point to Save As.

  2. Click Save Database As, click Back Up Database. Access closes the original file, creates a backup, and then reopens the original file.

  3. Click Save As and specify a name and location for the backup copy, and click Save.

Note: If you are using a read-only or a database created in the previous version of Access, you might get a message that it is not possible to create a back-up of the database.

To revert to a backup, close and rename the original file so that the backup copy can use the name of the original version. Assign the name of the original version to the backup copy, and open the renamed backup copy in Access.

Using a delete query

To create a delete query, click the Create tab, in the Queries group, click Query Design. Double-click each table from which you want to delete records, and then click Close.

The table appears as a window in the upper section of the query design grid. From the list of fields, double-click the asterisk (*) to add all of the fields in the table to the design grid.

Using a specific criteria in a delete query

Important: Use criteria to return only the records that you want to delete. Otherwise, the delete query removes every record in the table.

Double-click the field that you want to specify as the criteria for deletion, enter one the criteria in the Criteria row of the query designer, and then clear the Show check box for each criteria field.

An example of when you might want to use this option: Suppose you want to remove all of the pending orders for a customer. To find just those records, you’d add the Customer ID and Order Date fields to the query design grid, and then you enter the ID number of the customer, and the date on which that customer's orders became invalid.

  1. On the Design tab, click View > Datasheet View.

  2. Verify that the query returns the records that you want to delete, and then press CTRL+S to save the query.

  3. To run the query, double-click the query in the Navigation Pane.

Top of Page

Using an update query

Note: You cannot use the update query option in an Access web app.

This section explains how to use an update query to delete individual fields from tables. Remember that running an update query to delete data will change the existing values to either NULL or a zero-length string (a pair of double quotation marks with no space in between), depending on the criteria that you specify.

  1. Click the Create tab and in the Queries group, click Query Design.

  2. Select the table which has the data you want to delete (if the table is related, select the table on the "one" side of the relationship), click Add, and then click Close.

    The table appears as a window in the upper section of the query design grid. The window lists all of the fields in the selected table.

  3. Double-click the asterisk (*) to add all of the fields in the table to the design grid. Adding all the table fields enables the delete query to remove entire records (rows) from the table.

    Optionally, you can enter criteria for one or more fields in the Criteria row of the designer, and then clear the Show check box for each criteria field. For more information about using criteria, see the Sample criteria for select queries table.

    Note: You use criteria to return only the records that you want to change. Otherwise, the update query sets to NULL every record in each of the fields in your query.

  4. On the Design tab, in the Results group, click View, and then click Datasheet View.

  5. Verify that the query returns the records that you want to set to NULL or a zero-length string (a pair of double-quotation marks with no space between them ("").

  6. As needed, repeat steps 3 to 5 and change the fields or criteria until the query returns only the data that you want to delete, and then press CTRL+S to save the query.

  7. To run the query, double-click the query in the Navigation Pane.

Top of Page

Additional Information

Deleting data from related tables

If you want to delete data from several related tables, you must enable the Referential Integrity and Cascade Delete Related Records options for each relationship. This allows your query to delete data from the tables on the "one" and "many" sides of the relationship. Preparing to deleted related data requires verification of the following:

  • Determine which records reside on the "one" side of the relationship and which reside on the "many" side.

  • If you need to delete records on the "one" side of the relationship and the related records on the "many" side, you enable a set of rules called Referential Integrity, and you enable cascading deletes. Steps in this section explain Referential Integrity, and how to perform both tasks.

  • If you need to delete records only on the "one" side of the relationship, you first delete that relationship, and then delete the data.

If you need to remove data only on the "many" side of the relationship, you can create and run your delete query without having to change the relationship.

To find out which records reside on the "one" and "many" sides of a relationship, on the Database Tools tab, in the Relationships group, click Relationships. The Relationships tab displays the tables in your database and the relationships. Each relationship is depicted as a line that connects tables between fields.

The following figure shows a typical relationship. Most, if not all, of the relationships in a database have a "one" side and a "many" side. The relationship diagram denotes the "one" side with the numeral one (1) and the "many" side with the infinity () symbol.

a relationship between two tables

When you delete records on the "one" side of the relationship, you also delete all of the related records on the "many" side of the relationship. However, when you delete records on the "many" side of a relationship, you typically don't delete records on the "one" side.

Also, Access automatically enforces a set of rules called referential integrity. These rules ensure that the foreign keys in a database contain the correct values. A foreign key is a column whose values match the values in the primary key column of another table.

Edit the relationship

Follow these steps only when you need to delete data on the "one" and "many" sides of a relationship.

  1. On the Database Tools tab, in the Relationships group, click Relationships.

  2. Right-click the relationship (the line) connecting the tables involved in the deletion operation, and then click Edit Relationship on the shortcut menu.

  3. In the Edit Relationships dialog box, ensure that the Enforce Referential Integrity check box is selected.

  4. Select the Cascade Delete Related Records check box.

    Note: Until you disable this property again, deleting a record on the "one" side of the relationship will delete all of the related records on the "many" side of the relationship.

  5. Click OK, close the Relationships pane, and then go on to the next set of steps.

Delete a relationship

  1. If you haven't done so already, open the Relationships pane.

  2. On the Database Tools tab, in the Relationships group, click Relationships.

    Make a note of the fields involved in the relationship so that you can restore the relationship after you delete your data.

  3. Right-click the relationship (the line) connecting the tables involved in the deletion operation, and then click Delete on the shortcut menu.

Note: To restore the relationship, follow the previous steps to open the Relationships pane, and then drag the primary key field from the "one" table and drop it on the foreign key field of the "many" table. The Edit Relationship dialog box appears. If the old relationship enforced referential integrity, select Enforce Referential Integrity, and then click Create. Otherwise, just click Create.

Top of Page

Sample criteria for select queries

The following table lists some sample criteria that you can use in select queries when you want to make sure that you delete only the data that you want deleted. Some of these examples use wildcard characters.

Criteria

Effect

>

Returns all numbers greater than To find all numbers less than , use <

>= "Cajhen"

Returns all records from Cajhen through the end of the alphabet.

Between #2/2/# And #12/1/#

Returns dates from 2-Feb through 1-Dec (ANSI). If your database uses the ANSI wildcard characters, use single quotation marks (') instead of pound signs (#). Example: Between '2/2/' And '12/1/'.

Not "Germany"

Finds all records where the exact contents of the field are not exactly equal to "Germany." The criterion will return records that contain characters in addition to "Germany," such as "Germany (euro)" or "Europe (Germany)".

Not "T*"

Finds all records except those beginning with T. If your database uses the ANSI wildcard character set, use the percent sign (%) instead of the asterisk (*).

Not "*t"

Finds all records that do not end with t. If your database uses the ANSI wildcard character set, use the percent sign (%) instead of the asterisk (*).

In(Canada,UK)

In a list, finds all records containing Canada or UK.

Like "[A-D]*"

In a Text field, finds all records that start with the letters A through D. If your database uses the ANSI wildcard character set, use the percent sign (%) instead of the asterisk (*).

Like "*ar*"

Finds all records that include the letter sequence "ar". If your database uses the ANSI wildcard character set, use the percent sign (%) instead of the asterisk (*).

Like "Maison Dewe?"

Finds all records that begin with "Maison" and contain a 5-letter second string in which the first 4 letters are "Dewe" and the last letter is unknown. If your database uses the ANSI wildcard character set, use the underscore (_) instead of the question mark (?).

#2/2/#

Finds all records for February 2, If your database uses the ANSI wildcard character set, surround the date with single quotation marks instead of pound signs ('2/2/').

< Date() - 30

Uses the Date function to return all dates more than 30 days old.

Date()

Uses the Date function to return all records containing today's date.

Between Date() And DateAdd("M", 3, Date())

Uses the Date and the DateAdd functions to return all records between today's date and three months from today's date.

Is Null

Returns all records that contain a null (blank or undefined) value.

Is Not Null

Returns all records that contain any value (that are not null).

""

Returns all records that contain a zero-length string. You use zero-length strings when you need to add a value to a required field, but you don't yet know what the actual value is. For example, a field might require a fax number, but some of your customers might not have fax machines. In that case, instead of entering a number, you enter a pair of double quotation marks with no space between them ("").

Top of Page

Troubleshooting tips

Why am I seeing this error message, and how do I fix it?

If you build a delete query by using multiple tables and the query's Unique Records property is set to No, Access displays the error message; Could not delete from the specified tables when you run the query.

To fix this problem, set the query's Unique Records property to Yes.

  1. Open the delete query in Design view.

  2. If the query property sheet is not open, press F4 to open it.

  3. Click the query designer to show the query properties (rather than the field properties).

  4. In the query property sheet, locate the Unique Records property, and set it to Yes.

Stop Disabled Mode from blocking a query

By default, if you open a desktop database that you have not chosen to trust or that does not reside in a trusted location, Access blocks all action queries from running.

If you try to run an action query and it seems like nothing happens, check the Access status bar for the following message:

This action or event has been blocked by Disabled Mode.

When you see that message, take the following step to enable the blocked content:

  • On the Security Warning Message Bar, click Enable Content, and run your query again.

Top of Page

Sours: https://support.microsoft.com/en-us/office/create-and-run-a-delete-query-6da65fefcaef0-ccd4c3ec5

You will also like:

Delete data from an Access database by using a query

To delete large amounts of data quickly, or to regularly perform the same delete operations, consider using a query. A query can help you make sure that you are deleting the correct data, and can be saved and then easily repeated.

Note:&#; If you want to remove a small number of records &#; any quantity that you feel comfortable deleting by hand &#; you can open the table in Datasheet view, select the fields or rows that you want to delete, and press DELETE.

Important:&#; Make sure that you have a backup of your database before you delete the data.

In this article

Choose a query type based on the data you want to delete

Create and use a delete query

Create and use an update query

Deleting data from related tables

Sample criteria for select queries

Additional information

Choose a query type based on the data you want to delete

You have the option of using either an update query or a delete query depending on the type of deletion that you need to perform.

Delete query

Use a delete query to remove entire records (rows) from a table or from two related tables, in one operation. Delete queries remove all the data in each field, including the key value that makes a record unique.

Note:&#; If the records that you want to delete reside on the "one" side of a one-to-many relationship, you might need to change the relationship before you run the delete query. See the section Deleting data from related tables in this article.

Update query

Use an update query to delete individual field values from a table, An update query let you delete values by updating the existing values to either a null value (that is, no data) or a zero-length string (a pair of double quotation marks with no space between them).

Top of Page

What to verify before using a query to delete data

  • Ensure that the database file is not read-only. To do so, in Windows Explorer, right-click the database file and then click Properties.

  • Verify that you have the necessary permissions to delete records from the database. If you are not sure, contact your system administrator or the database designer.

  • Make sure that you have enabled content in the database. By default, Access blocks all action queries (delete, update, and make-table queries) unless you first trust the database. For information about trusting a database, see Stop Disabled Mode from blocking a query later in this article.

  • Ask other users of the database to close all tables, forms, queries, and reports that use the data that you want to delete. This helps avoid lock violations.

  • Before you edit or delete records, back up the database. You cannot reverse operations that are performed by delete and update queries, so making a backup copy ensures that you can always reverse your changes.

Tip:&#; If a large number of users connect to the database, you might need to close the database and then reopen it in Exclusive mode.

To open a database in Exclusive mode

  1. Click the File tab, and then click Open.

  2. Browse to and point to select the database, click the arrow next to the Open button, and then click Open Exclusive.

    Opening a file in Exclusive mode

Back up the database

  1. Click the File tab, point to Save & Publish, and then, under Save Database As, click Back Up Database.Access closes the original file, creates a backup, and then reopens the original file.

  2. In the Save As dialog box, specify a name and location for the backup copy, and then click Save.

To revert to a backup, close and rename the original file so that the backup copy can use the name of the original version. Assign the name of the original version to the backup copy, and open the renamed backup copy in Access.

Create and use a delete query

The steps in this section explain how to create and use a delete query to delete entire records from a table:

  1. On the Create tab, in the Queries group, click Query Design.

The Show Table dialog box appears.

  1. Double-click each table from which you want to delete records, and then click Close.

The table appears as a window in the upper section of the query design grid. The window lists all of the fields in the selected table.

  1. Double-click the asterisk (*) to add all of the fields in the table to the design grid.

Optionally, double-click any field that you want to use to specify criteria for deletion, enter one or more criteria in the Criteria row of the designer, and then clear the Show check box for each criteria field.

For example, suppose that a customer goes out of business and you need to remove all of the pending orders for that customer. To find just those records, you add the Customer ID and Order Date fields to the design grid, and then you enter the ID number of the customer that went out of business, and the date on which that customer's orders became invalid.

Important:&#; Use criteria to return only the records that you want to delete. Otherwise, the delete query removes every record in the table.

  1. On the Design tab, in the Results group, click View, and then click the Datasheet View.

  2. Verify that the query returns the records that you want to delete, and then press CTRL+S to save the query.

  3. To run the query, double-click the query in the Navigation Pane.

Top of Page

Create and use an update query

The steps in this section explain how to use an update query to delete individual fields from tables on the "many" side of a one-to-many relationship. You can also follow these steps to delete data from tables that are not related to other data. Remember that running an update query to delete data will change the existing values to either NULL or a zero-length string (a pair of double quotation marks with no space in between), depending on the criteria that you specify.

  1. On the Create tab, in the Queries group, click Query Design.

The Show Table dialog box appears.

  1. Select the table that contains the data you want to delete (if the table is related, select the table on the "one" side of the relationship), click Add, and then click Close.

The table appears as a window in the upper section of the query design grid. The window lists all of the fields in the selected table.

  1. Double-click the asterisk (*) to add all of the fields in the table to the design grid. Adding all the table fields enables the delete query to remove entire records (rows) from the table.

    Optionally, you can enter criteria for one or more fields in the Criteria row of the designer, and then clear the Show check box for each criteria field. For more information about using criteria, see the Sample criteria for select queries table.

Note:&#; You use criteria to return only the records that you want to change. Otherwise, the update query sets to NULL every record in each of the fields in your query.

  1. On the Design tab, in the Results group, click View, and then click Datasheet View.

  2. Verify that the query returns the records that you want to set to NULL or a zero-length string (a pair of double-quotation marks with no space between them ("").

  3. As needed, repeat steps 3 to 5 and change the fields or criteria until the query returns only the data that you want to delete, the then and then press CTRL+S to save the query.

  4. To run the query, double-click the query in the Navigation Pane.

Top of Page

Deleting data from related tables

If you want to delete data from several related tables, you must enable the Referential Integrity and Cascade Delete Related Records options for each relationship. This allows your query to delete data from the tables on the "one" and "many" sides of the relationship. Preparing to deleted related data requires verification of the following:

  • Determine which records reside on the "one" side of the relationship and which reside on the "many" side.

  • If you need to delete records on the "one" side of the relationship and the related records on the "many" side, you enable a set of rules called Referential Integrity, and you enable cascading deletes. Steps in this section explain Referential Integrity, and how to perform both tasks.

  • If you need to delete records only on the "one" side of the relationship, you first delete that relationship, and then delete the data.

    -or-

If you need to remove data only on the "many" side of the relationship, you can create and run your delete query without having to change the relationship.

Determine the relationships

To determine which records reside on the "one" and "many" sides of a relationship:

  • On the Database Tools tab, in the Relationships group, click Relationships.

The Relationships tab appears and displays all of the tables in your database and also the relationships between each table and every other table. Each relationship is depicted as a line that connects tables between fields.

The following figure shows a typical relationship. Most, if not all, of the relationships in a database have a "one" side and a "many" side. The relationship diagram denotes the "one" side with the numeral one (1) and the "many" side with the infinity (&#;) symbol.

a relationship between two tables

As a rule, when you delete records on the "one" side of the relationship, you also delete all of the related records on the "many" side of the relationship. However, when you delete records on the "many" side of a relationship, you typically don't delete records on the "one" side.

Also, by default, Access enforces a set of rules called referential integrity. These rules ensure that the foreign keys in a database contain the correct values. A foreign key is a column whose values match the values in the primary key column of another table.

Edit the relationship

Follow these steps only when you need to delete data on the "one" and "many" sides of a relationship.

  1. On the Database Tools tab, in the Relationships group, click Relationships.

  2. Right-click the relationship (the line) connecting the tables involved in the deletion operation, and then click Edit Relationship on the shortcut menu.

The Edit Relationships dialog box appears.

  1. Ensure that the Enforce Referential Integrity check box is selected.

  2. Select the Cascade Delete Related Records check box.

Note:&#; Until you disable this property again, deleting a record on the "one" side of the relationship will delete all of the related records on the "many" side of the relationship.

  1. Click OK, close the Relationships pane, and then go on to the next set of steps.

Delete a relationship

  1. If you haven't done so already, open the Relationships pane.

  2. On the Database Tools tab, in the Relationships group, click Relationships.

Make a note of the fields involved in the relationship so that you can restore the relationship after you delete your data.

  1. Right-click the relationship (the line) connecting the tables involved in the deletion operation, and then click Delete on the shortcut menu.

Note:&#;To restore the relationship, follow the previous steps to open the Relationships pane, and then drag the primary key field from the "one" table and drop it on the foreign key field of the "many" table. The Edit Relationship dialog box appears. If the old relationship enforced referential integrity, select Enforce Referential Integrity, and then click Create. Otherwise, just click Create.

Top of Page

Sample criteria for select queries

The following table lists some sample criteria that you can use in select queries when you want to make sure that you delete only the data that you want deleted. Some of these examples use wildcard characters.

Criteria

Effect

>

Returns all numbers greater than To find all numbers less than , use <

>= "Cajhen"

Returns all records from Cajhen through the end of the alphabet.

Between #2/2/# And #12/1/#

Returns dates from 2-Feb through 1-Dec (ANSI). If your database uses the ANSI wildcard characters, use single quotation marks (') instead of pound signs (#). Example: Between '2/2/' And '12/1/'.

Not "Germany"

Finds all records where the exact contents of the field are not exactly equal to "Germany." The criterion will return records that contain characters in addition to "Germany," such as "Germany (euro)" or "Europe (Germany)".

Not "T*"

Finds all records except those beginning with T. If your database uses the ANSI wildcard character set, use the percent sign (%) instead of the asterisk (*).

Not "*t"

Finds all records that do not end with t. If your database uses the ANSI wildcard character set, use the percent sign (%) instead of the asterisk (*).

In(Canada,UK)

In a list, finds all records containing Canada or UK.

Like "[A-D]*"

In a Text field, finds all records that start with the letters A through D. If your database uses the ANSI wildcard character set, use the percent sign (%) instead of the asterisk (*).

Like "*ar*"

Finds all records that include the letter sequence "ar". If your database uses the ANSI wildcard character set, use the percent sign (%) instead of the asterisk (*).

Like "Maison Dewe?"

Finds all records that begin with "Maison" and contain a 5-letter second string in which the first 4 letters are "Dewe" and the last letter is unknown. If your database uses the ANSI wildcard character set, use the underscore (_) instead of the question mark (?).

#2/2/#

Finds all records for February 2, If your database uses the ANSI wildcard character set, surround the date with single quotation marks instead of pound signs ('2/2/').

< Date() - 30

Uses the Date function to return all dates more than 30 days old.

Date()

Uses the Date function to return all records containing today's date.

Between Date() And DateAdd("M", 3, Date())

Uses the Date and the DateAdd functions to return all records between today's date and three months from today's date.

Is Null

Returns all records that contain a null (blank or undefined) value.

Is Not Null

Returns all records that contain any value (that are not null).

""

Returns all records that contain a zero-length string. You use zero-length strings when you need to add a value to a required field, but you don't yet know what the actual value is. For example, a field might require a fax number, but some of your customers might not have fax machines. In that case, instead of entering a number, you enter a pair of double quotation marks with no space between them ("").

Top of Page

Additional information

Why am I seeing this error message, and how do I fix it?

If you build a delete query by using multiple tables and the query's Unique Records property is set to No, Access displays the error message Could not delete from the specified tables when you run the query.

To fix this problem, set the query's Unique Records property to Yes.

  1. Open the delete query in Design view.

  2. If the query property sheet is not open, press F4 to open it.

  3. Click the query designer to show the query properties (rather than the field properties).

  4. In the query property sheet, locate the Unique Records property, and set it to Yes.

Top of Page

Stop Disabled Mode from blocking a query

By default, if you open a database that you have not chosen to trust or that does not reside in a trusted location, Access blocks all action queries from running.

If you try to run an action query and it seems like nothing happens, check the Access status bar for the following message:

This action or event has been blocked by Disabled Mode.

When you see that message, take the following step to enable the blocked content:

  • On the Security Warning Message Bar, click Enable Content, and run your query again.

&#;Top of Page

Sours: http://howtomicrosoftofficetutorials.blogspot.com//06/delete-data-from-access-database-by.html


527 528 529 530 531