Deleting unneeded info in a table.

[klufkee]

Hi all,

I've got 2 tables. One of them has 764 records, with one of the fields
having unique values. (I mean to say that each number is only listed
once.) it is related to another table with 33911 records. I've been
going through and getting rid of the unneeded records by hand but there
has got to be an easier way to do this. Each record in the smaller
table has the same value in 'TM_GIN' as one or more records in the
'TBL_GIN' field of the second, larger table. Is there a way to delete
all the records in the larger table that do NOT have a maching record
in the smaller table?

Thanks,
Bill

 

[Jerry Whittle]

Standard Warning: Make a backup of the tables involved or even the entire
database first.

DELETE Bill33911.TM_GIN
FROM Bill33911
WHERE Bill33911.TM_GIN
NOT IN (SELECT TM_GIN
FROM Bill764);

 

[Clarabelle]

I also strongly urge you to back up your tables before deleting anything.
Recently, I attended an ACCESS class and the instructor couldn't emphasize
enough that before deleting records/info., you should consider archiving the
information to be deleted. Murphy's law dictates that at some point you will
need part or all of the data deleted. Just helpful suggestions...

 

[klufkee]

Thanks!

The code;

DELETE Bill33911.TM_GIN
FROM Bill33911
WHERE Bill33911.TM_GIN
NOT IN (SELECT TM_GIN
FROM Bill764);

Is this something I should create using a Macro? or VB?
Please forgive me for what may seem to be a silly question but I really
don't know.
Also thanks for the back-up advice, I may be new to all of this but
that's one thing I did remember to do!

Bill

 

[Jerry Whittle]

Make your backup of the data first.

Go to the database window and on the objects bar select Queries.

Go to New, Design View and click OK.

At the Show Table dialog box click Close without adding any tables.

Go to View, Sql View. Paste in the SQL. Change the table names to the
'real' table names.

Go to View, Datasheet View. This will show you the records that it's going
to delete. If it runs at all, Good! If not figure out what is wrong. If it
does run, make sure that this is the data that you really want to delete.

If so go to View, Design View. Then Query, Run. You will get a warning.

If you plan on using this query in the future, click on the save button and
name it to something appropriate. Then you can just double-click on it to run
the query in the future.