I'm SOOO tired of delete queries failing!

[Bob]

In that case it is a Totals query - which is not updateable, nor is any query
including it.


Of course the individual tables are updateable; that doesn't mean that a
Totals query is updateable!

John W. Vinson [MVP]

John,

TX much for your very surprising reply!

I'm not sure I understand you...

are you saying that a perfectly updatable query becomes UN-updateable
when attached to a find-duplicates query?
if this is true, then why does attaching a table to the find-dup query
ALLOW deletions? because it's not a query?!

Here's an example of what the find-dup query is (this isn't the exact
query):
SELECT contact_id
FROM T_import_data
WHERE (((T_import_data.contact_id) In
(SELECT [contact_id] FROM [T_import_data] As Tmp GROUP BY [contact_id]
HAVING Count(*)>1 )))

 

[John W. Vinson]

are you saying that a perfectly updatable query becomes UN-updateable
when attached to a find-duplicates query?
if this is true, then why does attaching a table to the find-dup query
ALLOW deletions? because it's not a query?!

Here's an example of what the find-dup query is (this isn't the exact
query):
SELECT contact_id
FROM T_import_data
WHERE (((T_import_data.contact_id) In
(SELECT [contact_id] FROM [T_import_data] As Tmp GROUP BY [contact_id]
HAVING Count(*)>1 )))

Hm. In my experience ANY query joined to a find-dup query like this will not
be updateable, whether you're joining to a Table or to a preexisting query.
The problem is the GROUP BY in the subquery - that messes everything up!

Try

SELECT Contact_ID
FROM T_import_data
WHERE DCount("*", "[T_import_data]", "[Contact_ID] = " & [Contact_ID]) > 1

The DCount function is slower but won't interfere with updates.

John W. Vinson [MVP]

 

[Chris2]

Chris;

Here's the Q_patients query:
SELECT T_contacts.*, T_patients.*
FROM T_contacts INNER JOIN T_patients ON T_contacts.contact_id =
T_patients.contact_id
ORDER BY T_contacts.last_name;

I won't post the full table structure of the underlying tables -
they're huge... but here's what counts:

T_contacts:
(Key) contact_id autonumber
fname text
lname etc.....
several other fields in the table are indexed w/ duplicates OK

T_patients:
(Key) contact_id long integer
patient_id text
ssn text
etc...
again, 1 or more other fields may be indexed.

records are always on a 1 to 1 basis between these tables IF they
exist in the patient file. Not all contacts are patients.

Bob

Bob,

Here are your two original queries:

Here's the first query:
DELETE Q_patients.*
FROM Q_patients
WHERE (((Q_patients.T_contacts.contact_id) In (select
ZQ_delete_dups.contact_id from ZQ_delete_dups)));

Here's the 2nd query:
DELETE Q_patients.*
FROM Q_patients RIGHT JOIN ZQ_delete_dups ON
Q_patients.T_contacts.contact_id = ZQ_delete_dups.contact_id;


In the second case, joining Q_patients to a table expression (a query named
ZQ_delete_dups) where aggregation was done stops the ability to use a DELETE or UPDATE
because MS Access decrees that this is not possible (in most cases it just isn't).

In the first case, we can "combine" the first query and its underlying query, Q_patients
(this is not to say you have to arrange your queries this way, I am just doing this to
illustrate).


DELETE Q_patients.*
FROM (SELECT T_contacts.*, T_patients.*
FROM T_contacts
INNER JOIN
T_patients
ON T_contacts.contact_id = T_patients.contact_id)
WHERE (((Q_patients.T_contacts.contact_id)
IN (select ZQ_delete_dups.contact_id
from ZQ_delete_dups)));

I have recreated this scenario by example on my own example database.

The above does not work.

But changing the table-expression from a Two-Table Query to a single Table makes it work.


I have two main ideas about what may be going wrong. (I haven't gone in and specifically
tested them, so no, I am not saying that these are absolutely what went wrong.)

1) Foreign Key:

I suspect that T_patients.contact_id is a foreign key to T_contacts.contact_id in your
database.

You can't delete a row from T_contacts *unless* there are no values for
T_contacts.contact_id in T_patients.contact_id.

In this case, I am thinking there are such values in T_patients.contact_id.

If the foreign key does exist, what is the Cascading Delete option for the relationship in
your database? (I am wondering if it is "off".)

My settings for this are "off" in my example database, and I appear to be having largely
the same results as you are.


2) Naming:

It may be that MS Access is not able to resolve the naming in the above query (the naming
is very ambiguous looking to me, and I know what it is doing).

I strongly recommend the use of proper table aliases at all times, and that you avoid the
use of * except in actual DELETE queries (the Q_patients query has two * symbols).


My Idea:

Just run two DELETE queries. One on T_Patients (first), and one on T_Contacts (second,
since this seems to be the origin of ContactID). (If Cascading delete is "on" for the
foreign key (assuming there is one), you won't need to run a delete on T_Patients, those
records will be removed for you.)


Sincerely,

Chris O.

 

[David W. Fenton]

In my experience ANY query joined to a find-dup query like this
will not be updateable

Sometimes DISTINcTROW can fix that.

 

[Bob]

<big snip>












Bob,

Here are your two original queries:

Here's the first query:
DELETE Q_patients.*
FROM Q_patients
WHERE (((Q_patients.T_contacts.contact_id) In (select
ZQ_delete_dups.contact_id from ZQ_delete_dups)));

Here's the 2nd query:
DELETE Q_patients.*
FROM Q_patients RIGHT JOIN ZQ_delete_dups ON
Q_patients.T_contacts.contact_id = ZQ_delete_dups.contact_id;

In the second case, joining Q_patients to a table expression (a query named
ZQ_delete_dups) where aggregation was done stops the ability to use a DELETE or UPDATE
because MS Access decrees that this is not possible (in most cases it just isn't).

In the first case, we can "combine" the first query and its underlying query, Q_patients
(this is not to say you have to arrange your queries this way, I am just doing this to
illustrate).

DELETE Q_patients.*
FROM (SELECT T_contacts.*, T_patients.*
FROM T_contacts
INNER JOIN
T_patients
ON T_contacts.contact_id = T_patients.contact_id)
WHERE (((Q_patients.T_contacts.contact_id)
IN (select ZQ_delete_dups.contact_id
from ZQ_delete_dups)));

I have recreated this scenario by example on my own example database.

The above does not work.

But changing the table-expression from a Two-Table Query to a single Table makes it work.

I have two main ideas about what may be going wrong. (I haven't gone in and specifically
tested them, so no, I am not saying that these are absolutely what went wrong.)

1) Foreign Key:

I suspect that T_patients.contact_id is a foreign key to T_contacts.contact_id in your
database.

You can't delete a row from T_contacts *unless* there are no values for
T_contacts.contact_id in T_patients.contact_id.

In this case, I am thinking there are such values in T_patients.contact_id.

If the foreign key does exist, what is the Cascading Delete option for the relationship in
your database? (I am wondering if it is "off".)

My settings for this are "off" in my example database, and I appear to be having largely
the same results as you are.

2) Naming:

It may be that MS Access is not able to resolve the naming in the above query (the naming
is very ambiguous looking to me, and I know what it is doing).

I strongly recommend the use of proper table aliases at all times, and that you avoid the
use of * except in actual DELETE queries (the Q_patients query has two * symbols).

My Idea:

Just run two DELETE queries. One on T_Patients (first), and one on T_Contacts (second,
since this seems to be the origin of ContactID). (If Cascading delete is "on" for the
foreign key (assuming there is one), you won't need to run a delete on T_Patients, those
records will be removed for you.)

Sincerely,

Chris O.

Chris;
TX!
some REALLY good information here....

I agree with everything you said about my 2nd query - I only went that
route because my first choice didn't work.
my cascade delete IS turned off, but referential integrity IS ON.

I never thought to try combining the queries, I had foolishly assumed
that it would make no difference
I also agree with your comments about ambiguity. I may have omitted
some names from my posted code, to make it easier to read.

This has certainly been an educational experience!
TX again...
Bob

 

[Bob]

are you saying that a perfectly updatable query becomes UN-updateable
when attached to a find-duplicates query?
if this is true, then why does attaching a table to the find-dup query
ALLOW deletions? because it's not a query?!

Here's an example of what the find-dup query is (this isn't the exact
query):
SELECT contact_id
FROM T_import_data
WHERE (((T_import_data.contact_id) In
(SELECT [contact_id] FROM [T_import_data] As Tmp GROUP BY [contact_id]
HAVING Count(*)>1 )))

Hm. In my experience ANY query joined to a find-dup query like this will not
be updateable, whether you're joining to a Table or to a preexisting query.
The problem is the GROUP BY in the subquery - that messes everything up!

Try

SELECT Contact_ID
FROM T_import_data
WHERE DCount("*", "[T_import_data]", "[Contact_ID] = " & [Contact_ID]) > 1

The DCount function is slower but won't interfere with updates.

John W. Vinson [MVP]

John-

Great suggestion; seems so obvious now, don't know why I didn't think
to try that....

TX!

 

[Chris2]

Chris;
TX!
some REALLY good information here....

Bob,

You're welcome.

my cascade delete IS turned off, but referential integrity IS ON.

In a copy of your database, try turning Cascade Delete on and running a DELETE on
T_contacts.

I hope you are able to resolve this.


Sincerely,

Chris O.