Deleting records from one table contained in another?

[Guest]

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.

 

[Guest]

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
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.

Try this :

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

 

[Guest]

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

 

[John Spencer]

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

 

[Guest]

Thanks! Setting the "Unique Records" did it.

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

 

[David Stearns]

If you are referencing more than one table in your Delete Query you need to
set the Unique Records property to Yes. Once you do that you should have no
problem.

There is no need to resort to SQL statements.