Delete or Unmatched query?

[tigertim71]

In this example I have two tables:

Table A has numbers 1-10. Table B has numbers 1-4.

What I would like to to is to remove all records in Table B from Table A, so I am left with records 5-10.

I have already done this using an unmatched query, but would it be possible to do this using a quick delete in the query builder? If so, how, and is it possible to drag Table B to under Table A without selecting all the statements (as below)?

Additionally if it was an SQL statement would it be the following or is there a better way?:

DELETE xName1.id
FROM xName1
WHERE (((xName1.id) In (1,2,3,4)));

Thanks,

Tim

 

[khawar]

Use this Sql Statement

DELETE TableA.Id
FROM TableA
WHERE (((TableA.Id) In (select TableB.Id from TableB)));

 

[tigertim71]

Thanks Khawar,

Do you know how I would do this using the query interface? I tried dragging and dropping yesterday, but with no success.

 

[maxmangion]

when you are in the query design you can select View -> SQL and put the SQL statement there.

 

[khawar]

As Maxmangion said paste this Sql code in Sql View save and close query and then again open this query in design view query interface will be visible

 

[tigertim71]

delete or unmatched?

OK. Many Thanks. This has done the trick. It's just that I thought there was an easier way of doing this by dragging and dropping the ID of the second table in the query viewer.