MS Access Delete Query

[Vogel515]

Here is an example of what I'm trying to do:

I have two tables:

Table A
Col 1: ID
Col 2: Desc
Col 3: Financials

Table B
Col 1: ID

Table B contains a subset from Table A. I want to delete all rows in Table A that have ID's in Table B.

Thanks in advance.

 

[EagleKeeper]

One hsot deal or automatic

If one shot

Create a query of records in Table A where a:ID equals B:ID
then when the output of the query is presented, select all and delete.

Make sure all the fields in TAble A are displayed by the query.

 

[Vogel515]

Whoa, that was stupid of me. I have the select query set up and I completely forgot I could just edit the records there...

 

[Vogel515]

mmm

So when I delete the rows in the select query, it clears Table B and not Table A of those rows...

What am I doing wrong? There are actually three primary keys in each table...

Here is my query:

SELECT *

FROM
TableA

INNER JOIN TableB

ON (TableA .Prd =TableB.Prd)
AND (TableA .Prj = TableB.Prj)
AND (TableA .SetID = TableB.SetID);

 

[kyzen]

This should work in Access, maybe not. If it doesn't, try removing the word "distinct" first. It's been awhile since I did any SQL for Access...

delete from TableA
where ID in (select distinct ID from TableB)

EDIT: change "delete" to "select *" to test the query first of course 🙂

 

[brandonbull]

if you know how to write the sql for the delete command, build it in the sql view.

 

[Vogel515]

I tried to just write a SQL statement to delete the rows using the join, however Access gives me an error: "Could not delete from specified tables"

That's why I was attempting to using the design view.

 

[kyzen]

Is there a relation between the keys to TableB? If so, you may not be allowed to delete from TableA while there are related records in TableB.

 

[Vogel515]

Hello Kyzen,

I'm not sure I follow. Table A has relationships to other tables... TableB was just an upload from excel where I identified rows that do not need to be in TableA


- J

 

[Vogel515]

Ok I said screw it, I copied the entire document into excel, identified the rows to remove, removed them, then cleared the table in access, and appended the new table.


Solved.


I appreciate all the help!

 

[KLin]

Originally posted by: Vogel515
Ok I said screw it, I copied the entire document into excel, identified the rows to remove, removed them, then cleared the table in access, and appended the new table.


Solved.


I appreciate all the help!

You should learn how to do the delete in access so you don't have to do that everytime you want to do a delete. Practice makes perfect!

 

[Vogel515]

It's very rare I do anything in access, most of my work is in SQL and the system I'm working with will be migrated to SQL as soon as possible!