Doing an Action Query on One Record

No rule says that an action query must work on multiple records in a table. Any action query can perform its task on just one record in the table, provided that there's a way to uniquely identify the record. If the table has a primary key, isolating a record by a value in that field is simple.

For example, suppose that you have a table named Customers that contains an AutoNumber field named CustID that acts as the primary key. You can easily isolate any customer in the table by using the customer's CustID value as the Criteria entry for the CustID field. Figure 7-12 shows a delete query that uses such a criterion to delete only Customer #14 from the Customers table. The SQL statement reflects the criterion that the CustID field equals 14 by the addition of WHERE (((Customers.CustID) = 14)) to the SQL statement.

Figure 7-12:

Sample query to delete one record.

Figure 7-12:

Sample query to delete one record.

j^l DeleteOneRecord : Delete Query


DELETE Customer s.CustID


FROM Customers

WHER E (((Customers. CustID)= 14));


All the parentheses in that WHERE clause aren't really necessary, nor is the table name Customers. The WHERE clause could be written more simply as WHERE CustID=14 or with the field name in square brackets, as in WHERE [CustID]=14.

0 0

Post a comment