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.