Query I think?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables. One table has over 12, 000 records in it with about 20 fields. The other table has 500 records in it with only two fields. I want to remove those same 500 records from the table that has the 12,000. Both tables have a unique field "id #" . Isn't there a simple way to do it. When I try to query and chose both fields " id #" from both tables I get a window telling me I have chosen fields from records sources the wizard can not connect.. Any ideas???????????
 
I have two tables. One table has over 12, 000 records in it with about 20 fields. The other table has 500 records in it with only two fields. I want to remove those same 500 records from the table that has the 12,000. Both tables have a unique field "id #" . Isn't there a simple way to do it. When I try to query and chose both fields " id #" from both tables I get a window telling me I have chosen fields from records sources the wizard can not connect.. Any ideas???????????
Click to expand...

If these are local or linked Access tables:

DELETE bigtable.* FROM bigtable
INNER JOIN smalltable
ON bigtable.[ID #] = smalltable.[ID #];

Copy and paste this into the SQL window of a new query, edit the
tablenames, and run it... Back up your database first of course.

If these are non-Access tables, please explain...
 
Standard SQL Would be:

DELETE FROM BIGTABLE A WHERE ID IN (SELECT ID FROM SMALLTABLE B)
OR
DELETE FROM BIGTABLE A WHERE EXISTS (SELECT 'X' FROM SMALLTABLE B WHERE
B.ID=A.ID)

Pieter

fields. The other table has 500 records in it with only two fields. I want
to remove those same 500 records from the table that has the 12,000. Both
tables have a unique field "id #" . Isn't there a simple way to do it. When
I try to query and chose both fields " id #" from both tables I get a window
telling me I have chosen fields from records sources the wizard can not
connect.. Any ideas???????????
If these are local or linked Access tables:

DELETE bigtable.* FROM bigtable
INNER JOIN smalltable
ON bigtable.[ID #] = smalltable.[ID #];

Copy and paste this into the SQL window of a new query, edit the
tablenames, and run it... Back up your database first of course.

If these are non-Access tables, please explain...
Click to expand...
 
Standard SQL Would be:

DELETE FROM BIGTABLE A WHERE ID IN (SELECT ID FROM SMALLTABLE B)
OR
DELETE FROM BIGTABLE A WHERE EXISTS (SELECT 'X' FROM SMALLTABLE B WHERE
B.ID=A.ID)
Click to expand...

Correct, and both will work in Access. However, the Access query
optimizer does in fact support updateable Joins and processes them
faster (often MUCH MUCH faster) than EXISTS or IN clauses.

It is possible to work with Access on its own terms; it is not
obligatory to maintain absolute relational purity, though you can do
so if you wish!
 
Back
Top