delete query for unmatched records

[Joe Del Medico]

I have two tables A & B and I want to delete all the records in A that
are not in B. Can I do this in the query builder? It seems like a
simple problem.

I can easily find the records in A that aren't in B by using the query
wizard to come up with a query "A without matching B".

I added table A to the query builder and Query "A without matching B"
joined them with the index field (Loan Number) made the query a delete
query and
chose Table A.* in the grid. I am getting the error "Couldn't delete
from specified tables". It can't be a permission problem because I
have full control on the database.

The SQL view is this:

DELETE A.*
FROM A INNER JOIN [A Without Matching B] ON A.[Loan Number] = [A
Without Matching B].[Loan Number];

I could go through the table programatically and delete all loans in A
that come up in the query "A without matching B" but this seems very
time consuming.
Shouldn't there be a way to do this in the query builder or at least
with a written SQL statement?

Joe Del Medico jo*********@yahoo.com

[Roger]

this should do it

DELETE *
FROM tblA
WHERE (((tblA.loanNumber) Not In (SELECT loanNumber from tblB)));
jo*********@yahoo.com (Joe Del Medico) wrote in message news:<29**************************@posting.google. com>...

I have two tables A & B and I want to delete all the records in A that
are not in B. Can I do this in the query builder? It seems like a
simple problem.

I can easily find the records in A that aren't in B by using the query
wizard to come up with a query "A without matching B".

I added table A to the query builder and Query "A without matching B"
joined them with the index field (Loan Number) made the query a delete
query and
chose Table A.* in the grid. I am getting the error "Couldn't delete
from specified tables". It can't be a permission problem because I
have full control on the database.

The SQL view is this:

DELETE A.*
FROM A INNER JOIN [A Without Matching B] ON A.[Loan Number] = [A
Without Matching B].[Loan Number];

I could go through the table programatically and delete all loans in A
that come up in the query "A without matching B" but this seems very
time consuming.
Shouldn't there be a way to do this in the query builder or at least
with a written SQL statement?

Joe Del Medico jo*********@yahoo.com

[Joseph Del Medico]

Thanks Roger !
Joe

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!