Re: help with delete query

From: Gary Walter (garylwpleasenospam_at_wamego.net)
Date: 04/29/04 

Date: Thu, 29 Apr 2004 14:43:10 -0500

"Allen Clark" <acent1@nospamplease.com> wrote
> I have an Access 2000 db with many linked tables. I have determined that if
> the persons.first, persons.last, persons.zip, and email.emailaddr fields are
> all blank, the record was incorrectly entered and I would like to get rid of
> any record that matches these criteria. When I build the delete query, the
> SQL that is generated is as follows:
>
> DELETE Persons.*
> FROM Persons LEFT JOIN EMail ON Persons.id = EMail.Id
> WHERE (((IsNull([persons].[first]))<>False) AND
> ((IsNull([persons].[last]))<>False) AND ((IsNull([persons].[zip]))<>False)
> AND ((IsNull([email].[emailaddr]))<>False));
>
>
> If this same selection criteria is used in a simple query with a left outer
> join, and select only the Persons.ID field, I get a listing of 1676 records.
> If I convert it to a delete query, the only message I get is "could not
> delete from specified tables".
>
> Any ideas on what is going on here?
>

Hi Allen,

I believe you just need to include "DISTINCTROW"
since your query contains more than one table.

DELETE DISTINCTROW Persons.*
FROM Persons LEFT JOIN EMail
ON Persons.id = EMail.Id
WHERE
([persons].[first] IS NULL)
AND
([persons].[last] IS NULL)
AND
([persons].[zip] IS NULL)
AND
([email].[emailaddr] IS NULL);

(This will only remove records from Persons table)

ACC2000: Changes in Use of DISTINCTROW in Microsoft Access 2000
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 *** 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 ***
displays "Query Properties" in the title bar.
Set the UniqueRecords property to Yes.
Save the query, close it, and then run the query.
*********unquote*************

Please respond back if I have misunderstood.

Good luck,

Gary Walter