Conditional Delete Query

  • Thread starter Thread starter AccessStudent
  • Start date Start date
A

AccessStudent

I am having difficulties creating a delete query where I have two
tables, tblPayments and tblOriginalData, and want to delete records
from the former, IF the record is older than 2003 AND there is a
matching fk_recordnumber in the latter.

The table structures:
tblPayments
pkey
fk_recordnumber
strType
curAmount
dtmDate

tblOriginalData
pkey
fk_recordnumber
curAmount
curMed
curPHA

I need to delete all records from tblPayments where the date (dtmDate)
is before Jan 1, 2003 AND there is a matching fk_recordnumber in
tblOriginalData.

I created a query using the wizard which returned the following SQL:

DELETE tblPayments.*, tblPayments.dtmDate
FROM tblPayments LEFT JOIN tblOriginalData ON
tblPayments.fk_RecordNumber= tblOriginalData.fk_RecordNumber
WHERE (((tblPayments.dtmDate)<#1/1/2003#));

If I VIEW the query results, it shows the correct records to be
deleted, but when I RUN the query I get an error message stating
"Operation must be an updatable query".

I also modified the join in the query query which resulted in the
following SQL statement:

DELETE tblPayments.*, tblPayments.dtmDate
FROM tblPayments, tblOriginalData
WHERE (((tblPayments.dtmDate)<#1/1/2003# And
[tblOriginalData]![fk_RecordNumber]=[tblPayments]![fk_RecordNumber]));


Still get the same error message. Is there anything obvious that I am
missing?
 
Do you have a primary/foreign key relationship between
tblPayments.fk_RecordNumber and tblOriginalData.fk_RecordNumber. I think the
field in tblOriginalData needs to be a primary key.
 
In addition, I believe this KB excerpt may help.

from
http://support.microsoft.com/default.aspx?scid=kb;en-us;207761

**QUOTE**
Delete Queries
***************************************
When a delete query contains more than one table,
such as a query that deletes duplicate records from one of the tables,
the UniqueRecords property must be set to Yes for all versions of Microsoft Access.
**************************************
However, because the default value for UniqueRecords is No in Access 2000, you must
set the value of this property manually when you create a new delete query in Access
2000.

To do so, follow these steps:
Open the delete query in Design view.
If the property sheet is not already open, on the View menu, click Properties.
Click an empty area in the upper half of the query window so that the property sheet
displays "Query Properties" in the title bar.
Set the UniqueRecords property to Yes.
Save the query, close it, and then run the query.
For additional information about using a delete query to remove duplicate records,
click the article number below to view the article in the Microsoft Knowledge Base:

209183 ACC2000: How to Delete Duplicate Records from a Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;209183

**UNQUOTE***
 
Back
Top