Deleting records from one table contained in another?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

RichL - 06 Jul 2006 15:29 GMT

I am attempting to delete items in one table that exist in another using a
delete query and joining the appropriate fields from the two tables. Access
is telling me that it cannot delete from these tables. Is there another way
to accomplish this? The first table is used to import data weekly and then
explanations are added. The second is used as a source to update (using an
update query) explanations for items already imported before. It is possible
that there might be different explanations as time goes on and I only want to
keep the most recent records for the update.

Reply to this Message

Mathieu Pagé - 06 Jul 2006 15:52 GMT

RichL a écrit :

> I am attempting to delete items in one table that exist in another using a
> delete query and joining the appropriate fields from the two tables. Access
[quoted text clipped - 4 lines]
> that there might be different explanations as time goes on and I only want to
> keep the most recent records for the update.

Try this :

DELETE FROM tblA WHERE tblA.a IS IN (SELECT a FROM tblB);

Reply to this Message

RichL - 06 Jul 2006 16:15 GMT

I am not advanced in SQL/Visual Basic. I am only using the actual using the
capabilities within Access itself.

> RichL a écrit :
> > I am attempting to delete items in one table that exist in another using a
[quoted text clipped - 9 lines]
>
> DELETE FROM tblA WHERE tblA.a IS IN (SELECT a FROM tblB);

Reply to this Message

John Spencer - 06 Jul 2006 17:27 GMT

Well, SQL is part of Access. It is a major part of Access.

Suggest you post the SQL of the query that is not working for you.
-- Open in design view
-- Switch to SQL view (Menu View: SQL)
-- Copy the text and paste it into your post.

Tell us WHICH table you want to delete records from. Then someone should be
able to modify your query. I would guess that you have references to more
than one table in the query grid.

With your present query, you can try
-- Removing all fields that you are not setting criteria against (where)
-- Set FROM on one field from the table you want to delete records from
-- Set the query property Unique Records to YES

>I am not advanced in SQL/Visual Basic. I am only using the actual using
>the
[quoted text clipped - 20 lines]
>>
>> DELETE FROM tblA WHERE tblA.a IS IN (SELECT a FROM tblB);

Reply to this Message

RichL - 06 Jul 2006 18:37 GMT

Thanks! Setting the "Unique Records" did it.

> Well, SQL is part of Access. It is a major part of Access.
>
[quoted text clipped - 36 lines]
> >>
> >> DELETE FROM tblA WHERE tblA.a IS IN (SELECT a FROM tblB);

Reply to this Message