Delete one or more records from a database
Deleting inaccurate or obsolete data can make your database faster and easier to use. For example, if you move data from the previous calendar or fiscal quarter to a set of archive tables in another database, you can reduce the size of your working database and help it run faster. If you remove inaccurate or duplicate data, you can reduce business costs.
This article explains how to delete data from a Microsoft Office Access 2007 database. It also explains how to delete data from individual fields — a relatively simple process, and how to delete entire records — a process that can be much more complex. the article also explains how to delete data manually, and how to use delete queries.
Note: To understand and use the information in this article, you must first understand the concepts of primary and foreign keys. For introductory information about primary and foreign keys, see the article Database design basics.
In this article
About deleting data
Access provides several types or levels of data deletion. You can delete data from individual data points from a record, you can delete entire records from a table, and you can also delete entire tables.
You delete individual values when you want to make some or all the data points in a record blank but still leave the record itself (the row) in place in the table. Deleting a data value is relatively easy — you select part or all of the data in the field and press DELETE.
When you delete an entire record, you remove all of the data in each field as well as the key value that makes the record unique. This process removes the entire row from the table. You can delete individual records manually, or you can create delete queries that remove large numbers of records at once.
If the records you want to delete are not related to any other data in the database, the deletion process is fairly simple. You can select an entire row and press DELETE, or you can create and run a delete query. However, if the records to be deleted are related to other data, and if they reside on the "one" side of a one-to-many relationship, you must perform some additional steps — because by default, Access does not allow you to delete related data. Also, remember that once you delete a whole record, you cannot undo that deletion. For that reason, you should always back up your database before you delete data.
Deleting entire tables is not as complex a process as deleting data from fields or deleting entire records, but remember that when you delete an entire table, you can break the functionality of some — and possibly all — of your database if you remove the table incorrectly. In addition, you lose all the data in the deleted table permanently. For those reasons, you should always back up your database before you delete a table.
Finally, you can delete data in less time and with greater accuracy if you understand relational database design. If you are new to Access or to relational databases in general, see Database design basics.
The procedures in the following sections explain how to delete data from individual fields, how to delete entire records, and how to delete entire tables. Each section provides the background information that you need to complete a given task.
Delete the data in a field
You can delete the data in individual fields when you have tables or queries open in Datasheet view 
, and when you have data entry forms open in Form 
and Layout 
views.
Delete data from a datasheet
-
In the Navigation Pane, double-click the table or query that you want to use. This opens the table or query in Datasheet view.
-
Select the data that you want to delete.
You can highlight part or all of the data, or just position the cursor in the field.
-
If you select all of the data in the field, press DELETE.
-or-
On the Home tab, in the Records group, click Delete.
-or-
If you place the cursor in the field, press DELETE or BACKSPACE as needed.
Delete data from a form
-
In the Navigation Pane, double-click the form that you want to use. This opens the form in Form view.
-or-
In the Navigation Pane, select the form that you want to use, and on the Home tab, in the Views group, click View, and then click Layout View.
-
Select the data that you want to delete.
You can highlight part or all of the data, or just position the cursor in the field.
-
If you select all of the data in the field, press DELETE.
-or-
On the Home tab, in the Records group, click Delete.
-or-
If you place the cursor in the field, press DELETE or BACKSPACE as needed.
Notes: Access might display an error message and prevent you from deleting data. Several factors can stop the deletion:
-
By design, you cannot delete data from some types of queries For example, you cannot edit the data returned by a crosstab query, and you cannot edit or remove calculated fields — values that a formula calculates as you use your database, but that do not reside in a table.
-
You do not have the necessary permissions to delete data Contact your system administrator or database designer.
-
The field does not accept blank or null values If you have the necessary permissions, open the table in Design view, and then change the Required table property from Yes to No.
-
You are trying to delete information from a list In a datasheet for a form, you can always spot a list because Access places a down arrow at one end, like so:
. For information on deleting the values in a list, see the next section of this article.
-
Delete the data in a list
You can delete data from two types of lists: value lists and lookup lists. Value lists display a set of predefined values — values that you or the database designer enter manually. In contrast, lookup lists use a query to retrieve values from another table or another query, and those returned values then populate the list.
The items in a value list reside in the same table as the other values in a record. In contrast, the data in a lookup list resides in one or more other tables. To remove data from a value list, you open the table and edit the items.
Removing data from a lookup list requires additional steps, and those steps vary depending on whether the query for the lookup lists takes its data from a table or another query. If the query for the lookup list is based on a table, you identify that table and the field that contains the data that appear in the list. You then open the source table and edit the data in that field.
If the query for the lookup list is based on another query, you must open that second query, find the source table and field from which the second query takes its data, and change the values in that table.
The following procedures explain how to delete the data in both a value list and a lookup list.
Remove data from a value list
-
In the Navigation Pane, right-click the table that you want to change and click Design View on the shortcut menu.
-
In the upper section of the design grid, select the table field that contains the value list.
-
In the lower section of the grid, click the Lookup tab, and then locate the Row Source property.
By default, double quotation marks surround the items in a value list, and semicolons separate each item: "Excellent";"Fair";"Average";"Poor"
-
As needed, remove the items from the list. Remember to delete the quotation marks that surround each deleted item. Also, do not leave a leading semicolon, do not leave pairs of semicolons together (;;), and if you remove the last item in the list, delete the final semicolon.
Note: If you delete an item from a value list, and records in the table already use that deleted item, the deleted item remains part of the record until you change it. For example, suppose your company has a warehouse in City A, but then sells that building. If you remove "City A" from the list of warehouses, you will see "City A" in your table until you change those values.
Remove data from a lookup list
-
In the Navigation Pane, right-click the table that you want to change and click Design View on the shortcut menu.
-
In the upper section of the design grid, select the lookup field.
-
In the lower section of the design grid, click the Lookup tab and locate the Row Source Type and Row Source properties.
The Row Source Type property should display Table/Query, and the Row Source property must contain a query that references a table or another query.
Note: Queries for lookup fields always begin with the word SELECT.
Typically (but not always), a select query uses this basic syntax: SELECT [table_or_query_name].[field_name] FROM [table_or_query_name].
In this case, the query contains two clauses (SELECT and FROM). The first clause refers to a table and to a field in that table; conversely, the second clause refers only to the table. A point to remember: The FROM clause will always tell you the name of the source table or query. SELECT clauses might not always contain the name of a table or a query, although they always contain the name of at least one field. However, all FROM clauses must refer to a table or query.
-
Do one of the following:
-
If the query in the Lookup field references another query, click the Build button (On the Data tab of the property sheet, click
next) to open the query in Design view. Make note of the name of the table that appears in the upper section of the query designer, and then continue to step 4. -
If the query in the Lookup field references a table, make note of the name of the table, and then continue to step 4.
-
-
In the Navigation Pane, double-click the source table to open it in Datasheet view.
-
Locate the field that contains the data used in the lookup list, and then edit that data as needed.
Delete unrelated records from a database
When you delete whole records from a database, you delete the data in each field, and you also delete the key value — the value that makes the record unique. If the record is not related to any other records (that is, if the key value does not reside in any other records or tables in the database), you can delete the record by selecting it and pressing DELETE. If you want to delete a large number of unrelated records — more than you want to delete manually — you can use a delete query to remove them. The procedures in the following sections explain how to perform both tasks.
Delete an unrelated record manually
-
In Datasheet view, open, the table or query that contains the data that you want to delete.
-
Locate the record (the row) that you want to delete, and then click the Select All button — the square located at the left or right end of the record. You can select individual rows, or click and drag to select a contiguous set of rows, or press CTRL and click the mouse button to select multiple non-contiguous rows.
Access places a border around the entire record (the entire row) or block of selected records.
-
Press DELETE.
-or-
On the Home tab, in the Records group, click Delete.
-or-
Right-click the Select All button and click Delete Record.
If you can't click the Select All button, position the cursor in a field in the record. Then, on the Home tab, in the Records group, click the down arrow next to Delete and click Delete Record.
Note: At this point, Access might display an error message that says that you can't delete the record because it is related to other data. If you see such a message, click OK and go to Delete related records from a database, later in this article.
-
When Access prompts you to confirm the deletion, click Yes.
Delete unrelated records by using a delete query
When you use a delete query to remove data, you follow the same process for both unrelated and related records — you first create a select query, then ensure that it returns only the records that you want to delete, and finally convert the select query into a delete query. The background information and steps in the next section explain how to delete unrelated records.
Delete related records from a database
The process of deleting related records follows these general steps.
-
Plan the deletion. Make sure that you have the necessary permissions to delete the data, ensure that all other users have closed any objects that use the affected tables, and then back up the database.
-
Change the relationship between the tables involved in the deletion. Specifically, you enable the Referential Integrity and Cascade Delete Related Records options. This allows your deletion to cascade through all affected tables and to remove all related data.
-
Create a select query, and then add criteria as needed until the query returns the correct set of records.
-
Convert the select query to a delete query, and then use it to delete the data.
The following sections explain this process.
Plan and prepare for the deletion
Do the following tasks before you delete any records from a database.
-
Ensure that the database is not read-only. To review the status of the database, in Microsoft Windows Explorer, right-click the database file (the .accdb or .mdb file), and then click Properties on the shortcut menu. In the Properties dialog box, ensure that the Read-only check box is cleared.
-
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.
-
Check with other users of the database to ensure that the deletion will not affect them adversely.
-
Ask all 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.
Tip: If a large number of users connect to the database, you might need to close the database and reopen it in exclusive mode. To do so, click the Microsoft Office Button
, and then click Open. Browse to and select the database, click the arrow next to the Open button, and then click Open Exclusive.
-
Back up your database before you edit or delete records. Some delete operations can be reversed, but making a backup ensures that you can always reverse your changes.
-
Click the Microsoft Office Button
, point to Manage, and then, under Manage this database, click Back Up Database. -
In the Save Backup As dialog box, specify a name and location for the backup copy, and then click Save.
Access closes the original file, creates a backup, and then reopens the original file.
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.
-
-
As needed, identify and review the relationships involving the table from which you are deleting records. Follow these steps:
-
On the Database Tools tab, in the Show/Hide group, click Relationships.
The Relationships document tab appears and displays all of the tables in your database, plus the relationships between each table and every other table. The tab depicts each relationship as a line connecting tables.
The following figure depicts a typical set of relations. The Relationships document tab denotes the "one" side with the numeral one (1) and the "many" side with the infinity (∞) symbol. The following figure shows a typical relationship:
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. If you don't , you create orphaned records that have no meaning. However, when you delete records on the "many" side of a relationship, you typically don't delete records on the "one" side.
Also, the relationship might enforce a set of rules called referential integrity. The 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.
Learn about the referential integrity rules
-
Rule 1: You can't enter a value in the foreign key field of a related table unless that value already exists in the primary key of the primary table. However, you can enter a null value in the foreign key field.
-
Rule 2: You can't delete an entire record from a primary table if matching records exist in a related table.
Remember that you can work around this rule by enabling a property in the relationship between the primary and secondary tables. For more information about how to do so, see the next procedure.
-
Rule 3: You can't change a primary key value in the primary table if that record has related records.
However, you can also work around this rule by enabling a property in the relationship between the primary and secondary tables. For more information about how to do so, see the next procedure.
Unless you dictate otherwise, Access enforces these rules each time that you add, edit, or delete data in the database. When an action violates a rule, Access displays a message like the one shown in the following illustration, and cancels the action.
As you proceed, remember that although Access enables referential integrity by default in most cases, a database must first meet the following conditions:
-
To add a relationship between two tables that already have data, the existing data must conform to the rules of the relationship. For example:
-
The matching field from the primary table must either be a primary key or have a unique index.
-
The related fields in the primary and related table must both have the same data type.
Note: There are two exceptions to this rule. An AutoNumber field can be related to a Number field if that Number field has a FieldSize property setting of Long Integer. Also, an AutoNumber field that has a FieldSize property setting of Replication ID can be related to a Number field that has a FieldSize property setting of Replication ID.
-
Both tables belong to the same Access database.
Note: If the tables are linked, they must be in Access format. Also, you must open the database that contains the linked tables, and enable referential integrity. You cannot enforce referential integrity rules for linked tables from databases in other formats, such as Microsoft Office Excel 2007 workbooks. For information about enabling referential integrity, see the steps in the next section.
-
-
Make note of the name of the table fields on each side of the relationship.
-
Open each table, and review the data in each field to verify that the fields really do contain the data that you want to delete.
-
Leave the Relationships window open, and then go on to the steps in the next section.
-
Edit the relationship
-
If you haven't done so already, open the Relationships window.
On the Database Tools tab, in the Show/Hide group, click Relationships.
-
Right-click the relationship (the line) connecting the tables involved in the deletion operation, and then click Edit Relationship on the shortcut menu.
-or-
On the Design tab, in the Tools group, click Edit Relationships.
The Edit Relationships dialog box appears.
-
Ensure that the Enforce Referential Integrity check box is selected.
-
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 deletes all of the related records on the "many" side of the relationship.
-
Click OK, close the Relationships window, and then go on to the next set of steps.
Create a select query
-
On the Create tab, in the Other group, click Query Design.
The query designer opens, the Design tab appears, and the Show Table dialog box appears.
-
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 table. This figure shows a typical table in the designer.
-
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, add a table field that allows you to enter criteria.
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.
-
If you followed the preceding step, enter your criteria in the Criteria row of the designer grid.
You use criteria to return only the records that you want to delete. Otherwise, the delete query removes every record in the table. To continue the example from the preceding step, you enter the ID number of the customer that went out of business, and the date on which that customer's orders became invalid.
For more information about using criteria, see Sample query criteria, later in this article.
-
If you followed the preceding step, then clear the Show check box for each criteria field.
-
On the Design tab, in the Results group, click Run.
Verify that the query returns the records that you want to delete.
-
Keep the query open, and then go on to the next steps.
Convert the select query into a delete query and delete data
-
Click Design View to switch from the datasheet to the query designer.
-
On the Design tab, in the Query Type group, click Delete.
Access changes the select query to a delete query, hides the Show row in the lower section of the design grid, and adds the Delete row.
Ensure that the Delete row in the all fields (*) column displays From. The word Where should appear in any criteria columns.
-
Ensure that you want to delete the data, and then click Run
.Access prompts you to confirm the deletion.
Click Yes to delete the data.
Delete tables from a database
Remember that if you delete a database table, you cannot undo the deletion, so you should back up your database before you proceed. For information about backing up your database, see the Back up your database before you edit or delete records section, earlier in this article.
-
If you have the table open in any view (such as Design view or Datasheet view), close it.
-
In the Navigation Pane, right-click the table, and then click Delete on the shortcut menu.
Access asks you to confirm the deletion.
Click Yes.
If this table is related to one or more additional tables, Access asks if you want to delete those relationships.
-
Click Yes.
Access deletes any relationships, and then deletes the table.
Sample criteria for select queries
The following table lists some samples of the kinds of criteria that you can use in select queries to reduce the amount of data that the query returns.
| Criteria | Effect |
| > 234 | Returns all numbers greater than 234. To find all numbers less than 234, use < 234. |
| >= "Callahan" | Returns all records from Callahan through the end of the alphabet |
| Between #2/2/2006# And #12/1/2006# | Returns dates from 2-Feb-2006 through 1-Dec-2006 (ANSI-89). If your database uses the ANSI-92 wildcard characters, use single quotation marks (') instead of pound signs. Example: Between '2/2/2006' And '12/1/2006' |
| 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-92 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-92 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-92 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-92 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-92 wildcard character set, use the underscore (_) instead of the question mark. |
| #2/2/2006# | Finds all records for February 2, 2006. If your database uses the ANSI-92 wildcard character set, surround the date with single quotation marks instead of pound signs ('2/2/2006'). |
| < Date() - 30 | Returns all dates more than 30 days old. |
| Date() | Returns all records containing today's date. |
| Between Date() And DateAdd("M", 3, Date()) | Returns 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 (""). |
Microsoft Office Tutorials: Delete One Or More Records From A Database >>>>> Download Now
ReplyDelete>>>>> Download Full
Microsoft Office Tutorials: Delete One Or More Records From A Database >>>>> Download LINK
>>>>> Download Now
Microsoft Office Tutorials: Delete One Or More Records From A Database >>>>> Download Full
>>>>> Download LINK rS