Could not delete from specified tables, again

  • Thread starter Thread starter Rick Campbell
  • Start date Start date
R

Rick Campbell

Seems to be a common problem. I've read through many posts and guess my
problem is referencing the second table. My query for duplicates references
two fields and I can't seem to make the subquery. Can anyone help?

DELETE CCC1998.*, CCC1998.Address, CCC1998.[List Date], CCC1998.[MLS No]
FROM CCC1998 INNER JOIN dupes ON CCC1998.Address = dupes.Address
WHERE (((CCC1998.Address)=[dupes].[Address]) AND ((CCC1998.[List
Date])=[dupes].[List Date]) AND ((CCC1998.[MLS No])<>[dupes].[MinOfMLS
No]));


TIA!

Rick
 
Rick, a subquery in the WHERE clause will usually get you out of trouble
with DELETE queries:

DELETE FROM CCC1998 WHERE EXISTS
(SELECT dupes.Address FROM dupes
WHERE CCC1998.Address = [dupes].[Address]
AND CCC1998.[List Date]=[dupes].[List Date]
AND CCC1998.[MLS No]<>[dupes].[MinOfMLS No]);

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
 
Allen,

Thanks for the reference. I got the following to run:
DELETE * FROM CCC1998 WHERE Address IN
(SELECT dupes.Address FROM dupes
WHERE CCC1998.Address = [dupes].[Address]
AND CCC1998.[List Date]=[dupes].[List Date]
AND CCC1998.[MLS No]<>[dupes].[MinOfMLS No]);

But, it wants me to enter a Parameter Value for CCC1998.MLS No. If I do
nothing, the query returns nothing. If I enter a parameter, it returns the
whole database.


Allen Browne said:
Rick, a subquery in the WHERE clause will usually get you out of trouble
with DELETE queries:

DELETE FROM CCC1998 WHERE EXISTS
(SELECT dupes.Address FROM dupes
WHERE CCC1998.Address = [dupes].[Address]
AND CCC1998.[List Date]=[dupes].[List Date]
AND CCC1998.[MLS No]<>[dupes].[MinOfMLS No]);

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

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

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

Rick Campbell said:
Seems to be a common problem. I've read through many posts and guess my
problem is referencing the second table. My query for duplicates
references two fields and I can't seem to make the subquery. Can anyone
help?

DELETE CCC1998.*, CCC1998.Address, CCC1998.[List Date], CCC1998.[MLS No]
FROM CCC1998 INNER JOIN dupes ON CCC1998.Address = dupes.Address
WHERE (((CCC1998.Address)=[dupes].[Address]) AND ((CCC1998.[List
Date])=[dupes].[List Date]) AND ((CCC1998.[MLS No])<>[dupes].[MinOfMLS
No]));
Click to expand...
Click to expand...
 
Does the CCC1998 table contain a field named MLS No?
Is there exactly one space in that name?

The request for the parameter indicates that Access does not Access does not
recognise the field.

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

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

Rick Campbell said:
Allen,

Thanks for the reference. I got the following to run:
DELETE * FROM CCC1998 WHERE Address IN
(SELECT dupes.Address FROM dupes
WHERE CCC1998.Address = [dupes].[Address]
AND CCC1998.[List Date]=[dupes].[List Date]
AND CCC1998.[MLS No]<>[dupes].[MinOfMLS No]);

But, it wants me to enter a Parameter Value for CCC1998.MLS No. If I do
nothing, the query returns nothing. If I enter a parameter, it returns the
whole database.


Allen Browne said:
Rick, a subquery in the WHERE clause will usually get you out of trouble
with DELETE queries:

DELETE FROM CCC1998 WHERE EXISTS
(SELECT dupes.Address FROM dupes
WHERE CCC1998.Address = [dupes].[Address]
AND CCC1998.[List Date]=[dupes].[List Date]
AND CCC1998.[MLS No]<>[dupes].[MinOfMLS No]);

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Rick Campbell said:
Seems to be a common problem. I've read through many posts and guess my
problem is referencing the second table. My query for duplicates
references two fields and I can't seem to make the subquery. Can anyone
help?

DELETE CCC1998.*, CCC1998.Address, CCC1998.[List Date], CCC1998.[MLS No]
FROM CCC1998 INNER JOIN dupes ON CCC1998.Address = dupes.Address
WHERE (((CCC1998.Address)=[dupes].[Address]) AND ((CCC1998.[List
Date])=[dupes].[List Date]) AND ((CCC1998.[MLS No])<>[dupes].[MinOfMLS
No]));
Click to expand...
Click to expand...
Click to expand...
 
Yes, the field is there with one space. I tried removing the space and got
the same result. It is the key field for the table.



Allen Browne said:
Does the CCC1998 table contain a field named MLS No?
Is there exactly one space in that name?

The request for the parameter indicates that Access does not Access does
not recognise the field.

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

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

Rick Campbell said:
Allen,

Thanks for the reference. I got the following to run:
DELETE * FROM CCC1998 WHERE Address IN
(SELECT dupes.Address FROM dupes
WHERE CCC1998.Address = [dupes].[Address]
AND CCC1998.[List Date]=[dupes].[List Date]
AND CCC1998.[MLS No]<>[dupes].[MinOfMLS No]);

But, it wants me to enter a Parameter Value for CCC1998.MLS No. If I do
nothing, the query returns nothing. If I enter a parameter, it returns
the whole database.


Allen Browne said:
Rick, a subquery in the WHERE clause will usually get you out of trouble
with DELETE queries:

DELETE FROM CCC1998 WHERE EXISTS
(SELECT dupes.Address FROM dupes
WHERE CCC1998.Address = [dupes].[Address]
AND CCC1998.[List Date]=[dupes].[List Date]
AND CCC1998.[MLS No]<>[dupes].[MinOfMLS No]);

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Seems to be a common problem. I've read through many posts and guess my
problem is referencing the second table. My query for duplicates
references two fields and I can't seem to make the subquery. Can anyone
help?

DELETE CCC1998.*, CCC1998.Address, CCC1998.[List Date], CCC1998.[MLS
No]
FROM CCC1998 INNER JOIN dupes ON CCC1998.Address = dupes.Address
WHERE (((CCC1998.Address)=[dupes].[Address]) AND ((CCC1998.[List
Date])=[dupes].[List Date]) AND ((CCC1998.[MLS No])<>[dupes].[MinOfMLS
No]));
Click to expand...
Click to expand...
Click to expand...
 
If the field is present and of the same data type as MinOfMLS No, I'm not
sure what's going on.

You could try changing:
Address IN
to
EXISTS
since you already have that covered in the WHERE clause in the subquery.

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

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

Rick Campbell said:
Yes, the field is there with one space. I tried removing the space and got
the same result. It is the key field for the table.



Allen Browne said:
Does the CCC1998 table contain a field named MLS No?
Is there exactly one space in that name?

The request for the parameter indicates that Access does not Access does
not recognise the field.

Rick Campbell said:
Allen,

Thanks for the reference. I got the following to run:
DELETE * FROM CCC1998 WHERE Address IN
(SELECT dupes.Address FROM dupes
WHERE CCC1998.Address = [dupes].[Address]
AND CCC1998.[List Date]=[dupes].[List Date]
AND CCC1998.[MLS No]<>[dupes].[MinOfMLS No]);

But, it wants me to enter a Parameter Value for CCC1998.MLS No. If I do
nothing, the query returns nothing. If I enter a parameter, it returns
the whole database.


Rick, a subquery in the WHERE clause will usually get you out of
trouble with DELETE queries:

DELETE FROM CCC1998 WHERE EXISTS
(SELECT dupes.Address FROM dupes
WHERE CCC1998.Address = [dupes].[Address]
AND CCC1998.[List Date]=[dupes].[List Date]
AND CCC1998.[MLS No]<>[dupes].[MinOfMLS No]);

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Seems to be a common problem. I've read through many posts and guess
my problem is referencing the second table. My query for duplicates
references two fields and I can't seem to make the subquery. Can
anyone help?

DELETE CCC1998.*, CCC1998.Address, CCC1998.[List Date], CCC1998.[MLS
No]
FROM CCC1998 INNER JOIN dupes ON CCC1998.Address = dupes.Address
WHERE (((CCC1998.Address)=[dupes].[Address]) AND ((CCC1998.[List
Date])=[dupes].[List Date]) AND ((CCC1998.[MLS No])<>[dupes].[MinOfMLS
No]));
Click to expand...
Click to expand...
Click to expand...
 
Tried that. No joy. here's the code for the table which returns the distinct
records, maybe there's something in there, although it seems to work.

SELECT CCC1998.Address, CCC1998.Unit, CCC1998.[List Date], Min(CCC1998.[MLS
No]) AS [MinOfMLS No]
FROM CCC1998
GROUP BY CCC1998.Address, CCC1998.Unit, CCC1998.[List Date];

This is very frustrating. The database has about 7,000 duplicate records.


Allen Browne said:
If the field is present and of the same data type as MinOfMLS No, I'm not
sure what's going on.

You could try changing:
Address IN
to
EXISTS
since you already have that covered in the WHERE clause in the subquery.

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

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

Rick Campbell said:
Yes, the field is there with one space. I tried removing the space and
got the same result. It is the key field for the table.



Allen Browne said:
Does the CCC1998 table contain a field named MLS No?
Is there exactly one space in that name?

The request for the parameter indicates that Access does not Access does
not recognise the field.

Allen,

Thanks for the reference. I got the following to run:
DELETE * FROM CCC1998 WHERE Address IN
(SELECT dupes.Address FROM dupes
WHERE CCC1998.Address = [dupes].[Address]
AND CCC1998.[List Date]=[dupes].[List Date]
AND CCC1998.[MLS No]<>[dupes].[MinOfMLS No]);

But, it wants me to enter a Parameter Value for CCC1998.MLS No. If I do
nothing, the query returns nothing. If I enter a parameter, it returns
the whole database.


Rick, a subquery in the WHERE clause will usually get you out of
trouble with DELETE queries:

DELETE FROM CCC1998 WHERE EXISTS
(SELECT dupes.Address FROM dupes
WHERE CCC1998.Address = [dupes].[Address]
AND CCC1998.[List Date]=[dupes].[List Date]
AND CCC1998.[MLS No]<>[dupes].[MinOfMLS No]);

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Seems to be a common problem. I've read through many posts and guess
my problem is referencing the second table. My query for duplicates
references two fields and I can't seem to make the subquery. Can
anyone help?

DELETE CCC1998.*, CCC1998.Address, CCC1998.[List Date], CCC1998.[MLS
No]
FROM CCC1998 INNER JOIN dupes ON CCC1998.Address = dupes.Address
WHERE (((CCC1998.Address)=[dupes].[Address]) AND ((CCC1998.[List
Date])=[dupes].[List Date]) AND ((CCC1998.[MLS
No])<>[dupes].[MinOfMLS No]));
Click to expand...
Click to expand...
Click to expand...
 
You are trying to DELETE with a GROUP BY clause in the query? That won't
work.

I am assuming that CCC1998 is a table (not another query.)

If you are still stuck, another approach is to create a deduplicated table
to replace the existing one.
1. In the Database window, select the CCC1998 table, Copy (Ctrl+C), and
paste (Ctrl+V.) Indicate you want "Structure Only", and supply a new name
for the table.

2. In the design view of your query, change it to an Append query (Append on
Query menu.) Use First or Min for the fields that are not part of what
defines a duplidate. Map the fields from the old table to the new one.

3. Execute the query. Check that the new table is populated with the
unduplicated values.

4. Replace the old table with the new one.

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

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

Rick Campbell said:
Tried that. No joy. here's the code for the table which returns the
distinct records, maybe there's something in there, although it seems to
work.

SELECT CCC1998.Address, CCC1998.Unit, CCC1998.[List Date],
Min(CCC1998.[MLS No]) AS [MinOfMLS No]
FROM CCC1998
GROUP BY CCC1998.Address, CCC1998.Unit, CCC1998.[List Date];

This is very frustrating. The database has about 7,000 duplicate records.


Allen Browne said:
If the field is present and of the same data type as MinOfMLS No, I'm not
sure what's going on.

You could try changing:
Address IN
to
EXISTS
since you already have that covered in the WHERE clause in the subquery.

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

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

Rick Campbell said:
Yes, the field is there with one space. I tried removing the space and
got the same result. It is the key field for the table.



Does the CCC1998 table contain a field named MLS No?
Is there exactly one space in that name?

The request for the parameter indicates that Access does not Access
does not recognise the field.

Allen,

Thanks for the reference. I got the following to run:
DELETE * FROM CCC1998 WHERE Address IN
(SELECT dupes.Address FROM dupes
WHERE CCC1998.Address = [dupes].[Address]
AND CCC1998.[List Date]=[dupes].[List Date]
AND CCC1998.[MLS No]<>[dupes].[MinOfMLS No]);

But, it wants me to enter a Parameter Value for CCC1998.MLS No. If I
do nothing, the query returns nothing. If I enter a parameter, it
returns the whole database.


Rick, a subquery in the WHERE clause will usually get you out of
trouble with DELETE queries:

DELETE FROM CCC1998 WHERE EXISTS
(SELECT dupes.Address FROM dupes
WHERE CCC1998.Address = [dupes].[Address]
AND CCC1998.[List Date]=[dupes].[List Date]
AND CCC1998.[MLS No]<>[dupes].[MinOfMLS No]);

If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066

Seems to be a common problem. I've read through many posts and guess
my problem is referencing the second table. My query for duplicates
references two fields and I can't seem to make the subquery. Can
anyone help?

DELETE CCC1998.*, CCC1998.Address, CCC1998.[List Date], CCC1998.[MLS
No]
FROM CCC1998 INNER JOIN dupes ON CCC1998.Address = dupes.Address
WHERE (((CCC1998.Address)=[dupes].[Address]) AND ((CCC1998.[List
Date])=[dupes].[List Date]) AND ((CCC1998.[MLS
No])<>[dupes].[MinOfMLS No]));
Click to expand...
Click to expand...
Click to expand...
 
Back
Top