Delete Query

[Amy A]

I am having problems running a delete query when
the "where" criteria is a link from another table in the
database. I have tried several iterations of this to no
avail. Acess keeps telling me that it can not delete the
records from the table. Anybody have any ideas?

Thanks.

Amy

 

[Tom Ellison]

Dear Amy:

More information, please! And precisely what do you mean by "a link
from another table." This is not standard, precise terminology. Do
you mean an INNER JOIN?

Post the SQL of what you have so far, and explain in a little detail
what it is you want to accomplish, especially what the "link" does.

I am having problems running a delete query when
the "where" criteria is a link from another table in the
database. I have tried several iterations of this to no
avail. Acess keeps telling me that it can not delete the
records from the table. Anybody have any ideas?

Thanks.

Amy

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Amy A]

Tom,

Sorry for the lack of terminology. Here is what I am
trying to accomplish:

I have 2 tables. Table 1 has 35000 records, Table 2 has 1
record. I join these 2 tables in my delete query. I want
to delete from Table 1 the record that has the matching
record in Table 2. In my criteria field I have a build
that points to the corresponding field in Table 2. I have
read about DISTINCTROW and think that is my solution, but
am not sure how to apply it.

Thanks.

Amy

 

[Tom Ellison]

Dear Amy:

DELETE *
FROM Table1 T1
WHERE EXISTS
(SELECT * FROM Table2 T2
T2.SomeColumn = T1.SomeColumn
AND T2.AnotherColumn = T1.AnotherColumn)

The above would do what you want, and allows for two columns to match
between the two table.

Tom,

Sorry for the lack of terminology. Here is what I am
trying to accomplish:

I have 2 tables. Table 1 has 35000 records, Table 2 has 1
record. I join these 2 tables in my delete query. I want
to delete from Table 1 the record that has the matching
record in Table 2. In my criteria field I have a build
that points to the corresponding field in Table 2. I have
read about DISTINCTROW and think that is my solution, but
am not sure how to apply it.

Thanks.

Amy

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Amy A]

Dear Tom,

You are a genious!!

Thank you so much.

Amy