help with delete query

  • Thread starter Thread starter Allen Clark
  • Start date Start date
A

Allen Clark

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(.[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?

Thanks in advance,
Allen
 
Hi Allen

It is generally easier to make a new table with the deduplicated records
than to try to get Access to delete some of the duplicates from the existing
table but retain one of them.

Change your query from a Delete query to a Make Table query (Make Table on
Query menu).
 
Sorry for the confusion, but this is not to delete duplicates. This is to
delete totally useless records that have no valid information in them at
all. I am converting from Access to SQL and I don't want to try to bring
over thousands of records that were incorrectly added. I use the previously
mentioned criteria to identify which records are blank. Now, all I would
like to do is to delete all of these identified records.

As always, any assistance is greatly appreciated.

Thanks in advance,
Allen

Allen Browne said:
Hi Allen

It is generally easier to make a new table with the deduplicated records
than to try to get Access to delete some of the duplicates from the existing
table but retain one of them.

Change your query from a Delete query to a Make Table query (Make Table on
Query menu).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen Clark said:
I have an Access 2000 db with many linked tables. I have determined
Click to expand...
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
Click to expand...
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
Click to expand...
((IsNull([persons].[zip])) said:
AND ((IsNull(.[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?

Thanks in advance,
Allen[/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
 
Still easiest to make a table that has the valid stuff.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Allen Clark said:
Sorry for the confusion, but this is not to delete duplicates. This is to
delete totally useless records that have no valid information in them at
all. I am converting from Access to SQL and I don't want to try to bring
over thousands of records that were incorrectly added. I use the previously
mentioned criteria to identify which records are blank. Now, all I would
like to do is to delete all of these identified records.

As always, any assistance is greatly appreciated.

Thanks in advance,
Allen

Allen Browne said:
Hi Allen

It is generally easier to make a new table with the deduplicated records
than to try to get Access to delete some of the duplicates from the existing
table but retain one of them.

Change your query from a Delete query to a Make Table query (Make Table on
Query menu).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen Clark said:
I have an Access 2000 db with many linked tables. I have determined
Click to expand...
that
if
the persons.first, persons.last, persons.zip, and email.emailaddr
Click to expand...
fields
are
all blank, the record was incorrectly entered and I would like to get
Click to expand...
rid
of
any record that matches these criteria. When I build the delete
Click to expand...
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
Click to expand...
((IsNull([persons].[zip])) said:
AND ((IsNull(.[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?

Thanks in advance,
Allen[/QUOTE]
[/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
Allen Clark said:
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(.[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?
[/QUOTE]

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 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.
*********unquote*************

Please respond back if I have misunderstood.

Good luck,

Gary Walter
Click to expand...
 
First, I would use field Is Null as my criteria.

DELETE 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
.[emailaddr] is Null

Second, I would check that the SQL was as above and not


DELETE * ...

or

DELETE Persons.*, * ...

Those two statements both have a problem in that they refer to more than one table.
 
From the design view, I started the query definition by selecting the tables
Persons and Email. I then went into the SQL view where the following SQL
structure had been started:

SELECT
FROM Persons INNER JOIN EMail ON Persons.id = EMail.Id;

I then added the criteria exactly as you indicated and added in the select
the field Persons.ID so I could generate a quick list to see what would be
found. I went to the tables view and found that 1676 records were found
matching the specified criteria. I went back into the SQL view, and the
syntax was changed to what was printed. I changed the query from a select
query to a delete query and went into the SQL view. I was not aware of the
DISTINCTROW statement that Gary Walter recommended, and will try it with a
copy of my database to make sure that all functions correctly before running
it on the real deal.

Thank you, John and Gary, for your input. I will check back with the
results.

Thanks again,
Allen


John Spencer (MVP) said:
First, I would use field Is Null as my criteria.

DELETE 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
.[emailaddr] is Null

Second, I would check that the SQL was as above and not


DELETE * ...

or

DELETE Persons.*, * ...

Those two statements both have a problem in that they refer to more than one table.



[QUOTE="Allen"]
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[/QUOTE] [QUOTE="((IsNull([persons].[zip]))"]
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?

Thanks in advance,
Allen[/QUOTE][/QUOTE]
Click to expand...
 
Back
Top