I'm SOOO tired of delete queries failing!

[Bob]

running access 2k;

I have 2 queries - BOTH should work as far as I can see, and NEITHER
does; each reporting "could not delete from specified table".

Obviously, they are 2 attempts to complete the same deletion task,
which is to delete every record found in the ZQ_delete-dups recordset
from the Q_patients recordset.
Sounds simple, looks simple, s/b simple, but apparently it's not. I
believe either of these would work under sql-server....

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;


TIA- Bob

 

[Gary Walter]

Hi Bob,

I'd have thought (test on backup)

DELETE DISTINCTROW Q_patients.*
FROM
Q_patients
INNER JOIN
ZQ_delete_dups
ON
Q_patients.T_contacts.contact_id = ZQ_delete_dups.contact_id;

(you need DISTINCTROW when more than one table in Jet)

good luck,

gary

 

[Lynn Trapp]

Do you have a field in your Q_patients table named T_contacts.contact_id?

 

[Bob]

Hi Lynn,

I appreciate your reply!

Yes, I do have that field present in Q_patients.
If it helps, this is what Q_patients looks like:

SELECT T_contacts.*, T_patients.*
FROM T_contacts INNER JOIN T_patients ON T_contacts.contact_id =
T_patients.contact_id;

Bob

 

[Bob]

Hi Gary,

TX for your reply...

I had already designed the ZQ_delete_dups to return only the rows to
be deleted, therefore I'm uncertain as to how or why distinctrow
should matter in jet....
that being said, I of course tried your suggestion, but to no
avail.... same error message....

Bob

 

[Bob]

FYI:

As I mentioned in a previous post, Q_patients simply links 2 tables,
contacts & patients.
If I run my delete query twice; first on patients, and then on
contacts, it DOES work.

I don't know why it won't work with the Q_patients query, because I
can CERTAINLY add and delete records directly in that query.....

Bob

 

[Lynn Trapp]

So is Q_Patients a query?

 

[Albert D. Kallal]

DELETE Q_patients.*

FROM Q_patients
WHERE (((Q_patients.T_contacts.contact_id) In (select
ZQ_delete_dups.contact_id from ZQ_delete_dups)));

Gee, I wonder if it is the "." that is confusing this:

Try:

delete * from Q_Patients where [T_Contacts.contact_id]
in (select contact_id from ZQ_Delete_dups);

Since the "." is a table delimiter, I would avoid them....

I just tried the follwign query:

delete * from tblImport
where id in (select id from tblResultList);

It works fine for me...

 

[John W. Vinson]

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;

Well, you certainly DON'T want a Right Join - to limit the deletions to those
in ZQ_Delete_Dups you need an Inner Join.

I suspect the problem is the nature of ZQ_Delete_Dups. Is it a Totals query?
If so, no query containing it will be updateable. Could you post the SQL of
*that* query?

John W. Vinson [MVP]

 

[Chris2]

running access 2k;

I have 2 queries - BOTH should work as far as I can see, and NEITHER
does; each reporting "could not delete from specified table".

Obviously, they are 2 attempts to complete the same deletion task,
which is to delete every record found in the ZQ_delete-dups recordset
from the Q_patients recordset.
Sounds simple, looks simple, s/b simple, but apparently it's not. I
believe either of these would work under sql-server....

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;


TIA- Bob

Bob,

What happens, in both cases, when you change DELETE to SELECT?

Do both queries, when changed to SELECT, list the rows you wish to
delete?

My guess is that they do not, and that they will return a message that
relates to an ambiguous name somewhere.


Sincerely,

Chris O.

 

[Michel Walsh]

It is a matter of tracking the initial records involved in the join, not a
matter to really get distinct records.

It is probable that one of your queries is NOT delete-able, itself (ie, we
cannot track the (unique, one) record in the table to be deleted). Note that
GROUP BY, DISTINCT, UNION, or aggregated queries are not updateable. If your
query implies an not updateable query, even if it does not try to update
through it, that flags your main query as not updateable too.


Vanderghast, Access MVP

 

[David W. Fenton]

DELETE Q_patients.*
FROM Q_patients
WHERE (((Q_patients.T_contacts.contact_id) In (select
ZQ_delete_dups.contact_id from ZQ_delete_dups)));

Gee, I wonder if it is the "." that is confusing this:

Try:

delete * from Q_Patients where [T_Contacts.contact_id]
in (select contact_id from ZQ_Delete_dups);

Since the "." is a table delimiter, I would avoid them....

I just tried the follwign query:

delete * from tblImport
where id in (select id from tblResultList);

It works fine for me...

There should be no problems with table.* -- it is equivalent to *
only when you have only one table in your FROM clause.

 

[Marshall Barton]

DELETE Q_patients.*
FROM Q_patients
WHERE (((Q_patients.T_contacts.contact_id) In (select
ZQ_delete_dups.contact_id from ZQ_delete_dups)));

Gee, I wonder if it is the "." that is confusing this:

Try:

delete * from Q_Patients where [T_Contacts.contact_id]
in (select contact_id from ZQ_Delete_dups);

Since the "." is a table delimiter, I would avoid them....

There should be no problems with table.* -- it is equivalent to *
only when you have only one table in your FROM clause.

David, I think Albert is referring to the extra dot in:
Q_patients.T_contacts.contact_id

Either that is an ill formed referenced or it needs [ ]
around two of the three parts.

 

[Bob]

yes.

So is Q_Patients a query?

 

[Bob]

TX, Albert,

I'll give that a try; although, at least with access 2k;
I've had problems using * in delete queries...

DELETE Q_patients.*
FROM Q_patients
WHERE (((Q_patients.T_contacts.contact_id) In (select
ZQ_delete_dups.contact_id from ZQ_delete_dups)));

Gee, I wonder if it is the "." that is confusing this:

Try:

delete * from Q_Patients where [T_Contacts.contact_id]
in (select contact_id from ZQ_Delete_dups);

Since the "." is a table delimiter, I would avoid them....

I just tried the follwign query:

delete * from tblImport
where id in (select id from tblResultList);

It works fine for me...

 

[Bob]

ZQ_delete_dups is a standard show the duplicate records type of query.

I would argue the problem can't be with this query, because the delete
query works fine on the individual tables that makeup the Q_patients
query.

 

[Bob]

yes, I always create a select before converting it into a delete
query.
the select's ALWAYS work the way I want them to.

ONLY after turning them into a delete query do I ever have
problems....

 

[Chris2]

yes, I always create a select before converting it into a delete
query.
the select's ALWAYS work the way I want them to.

ONLY after turning them into a delete query do I ever have
problems....

Bob,

If it isn't an ambigous name, then let's see the SQL of underlying query, Q_patients, and
hopefully also the table structure of whatever Q_patients is based on.


Sincerely,

Chris O.

 

[John W. Vinson]

ZQ_delete_dups is a standard show the duplicate records type of query.

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

I would argue the problem can't be with this query, because the delete
query works fine on the individual tables that makeup the Q_patients
query.

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


John W. Vinson [MVP]

 

[Bob]

Bob,

If it isn't an ambigous name, then let's see the SQL of underlying query, Q_patients, and
hopefully also the table structure of whatever Q_patients is based on.

Sincerely,

Chris O.

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