Deleting data from tables

[C Tate]

I have an Access database containing a number of tables. The database
contains the names and 'identifiers' (consisting of two initials, a date of
birth, and sex, eg CT3/10/30M) of clients worked with. Incidentally, the
identifer is not the primary key so sometimes a client will appear in a
table more than once if they have used a service more than once.

I also have a Word document which consists of a list of clients and their
identifiers who should remain in the Access database.

All the other clients have to be deleted from the Access database.

I therefore need to compare the Word list and one of the Access tables and
delete all the people NOT on the Word list. Help! Is there a quick way to do
this?? I suppose it just involves comparing two lists but am not quite sure
how to do this. Doing it manually seems very laborious given that there are
thousands of people on the database.

 

[Dirk Goldgar]

I have an Access database containing a number of tables. The database
contains the names and 'identifiers' (consisting of two initials, a
date of birth, and sex, eg CT3/10/30M) of clients worked with.
Incidentally, the identifer is not the primary key so sometimes a
client will appear in a table more than once if they have used a
service more than once.

I also have a Word document which consists of a list of clients and
their identifiers who should remain in the Access database.

All the other clients have to be deleted from the Access database.

I therefore need to compare the Word list and one of the Access
tables and delete all the people NOT on the Word list. Help! Is there
a quick way to do this?? I suppose it just involves comparing two
lists but am not quite sure how to do this. Doing it manually seems
very laborious given that there are thousands of people on the
database.

Your first step is to either link or import the list from Word as an
Access table. Since I don't know the format of the list in the
document, I can't advise you exactly how to do this. It may be simplest
to copy the Word list, paste it into a text file, clean it up as need
be, and then from Access use File -> Get External Data -> Import... to
import the text file into a new table.

Let's suppose you have successfully imported or linked the "keep" list
into a table named "Keepers", which contains the field ClientID for
matching against your other table(s). I'll suppose for example that the
table you want to cleanse is called "ClientServices". Your second step
is then to run a delete query with SQL along these lines:

DELETE ClientServices.*
FROM ClientServices LEFT JOIN Keepers
ON ClientServices.ClientID = Keepers.ClientID
WHERE Keepers.ClientID Is Null;

That says, effectively, "Use the ClientID in each table to match table
ClientServices against Keepers, including in the initial results all
records from ClientServices, even those that are not matched. From this
result set, delete those that were unmatched."

I don't know how many tables in your database you need to clean up in
this fashion. Once you have imported or linked the Keepers, you can run
the above query for each table, merely changing the table name
"ClientServices" each time to the name of the table you want to cleanse.

 

[C Tate]

Many thanks for the help. I did try this but I got a message saying
something like 'Access failed to delete any records'.

I have created a list of identifiers in Excel. I still want to delete anyone
from my Access table NOT in the Excel list. Do you have any other
suggestions as to how to do this?

I was going to attempt to do it using an unmatched query. However, I
discovered that part of Access does not seem to be installed. Trying to get
my IT dept to do anything seems to take an eternity so I am wondering if I
can create my own unmatched query?

As I say, all I have is the Excel list consisting of a single column of
identifiers. My Access table has at least 30 columns, only one of which is
the 'identifer' column

 

[Dirk Goldgar]

Many thanks for the help. I did try this but I got a message saying
something like 'Access failed to delete any records'.

Hmm. That suggests that the syntax of the query was correct, but no
matching records were found. I wonder why? Please post the exact SQL
of the delete query you ran.

I have created a list of identifiers in Excel. I still want to delete
anyone from my Access table NOT in the Excel list. Do you have any
other suggestions as to how to do this?

This is really the best way to do it, so all we have to do is figure out
why the list doesn't match the table. Did you import the list from
Excel into a table, or link to it? Please post the table descriptions
(table name, and the name, type, and field size of each relevant field).
Also post a small sample (4 or 5 records) of the records from each
table.

I was going to attempt to do it using an unmatched query. However, I
discovered that part of Access does not seem to be installed. Trying
to get my IT dept to do anything seems to take an eternity so I am
wondering if I can create my own unmatched query?

The SQL I gave you *is* a "find unmatched" query, transformed into a
delete query. The equivalent as a select query would be:

SELECT ClientServices.*
FROM ClientServices LEFT JOIN Keepers
ON ClientServices.ClientID = Keepers.ClientID
WHERE Keepers.ClientID Is Null;

 

[C Tate]

Sorry I have taken so long to reply. The problem I am having is at work but
I am writing to you from home. I will get the code I have used and some
sample data as soon as I am back at work. In the meantime very many thanks
for your assistance. It is much appreciated! Incidentally I linked to Excel
rather than importing. But more on that later.

 

[C Tate]

I have had a chance to look at the delete query again. Now! Here is an odd
thing. The select query you gave me DOES work but the delete one doesn't.
Why might this be?

I am not sure it is practical to post a description of the Access table. It
has over 30 columns and it would take a long time to type it all out. Most
of these columns are actually just text fields of about 50 characters max.
The linked Excel table has only one column called Identifier. The Access
table also has this column.

In fact John Vinson has provided me with a solution which does work:

DELETE * FROM yourtable
WHERE ClientID NOT IN
(SELECT ClientID FROM LinkedExcelTable);

That is all well and good BUT I am still very curious to know why your
solution won't work. Would it have anything to do with the fact that the
Access table I am trying to delete from is also a linked table? Don't ask me
WHAT it is linked to! I haven't figured that one out yet!

 

[Dirk Goldgar]

I have had a chance to look at the delete query again. Now! Here is
an odd thing. The select query you gave me DOES work but the delete
one doesn't. Why might this be?

I am not sure it is practical to post a description of the Access
table. It has over 30 columns and it would take a long time to type
it all out. Most of these columns are actually just text fields of
about 50 characters max. The linked Excel table has only one column
called Identifier. The Access table also has this column.

In fact John Vinson has provided me with a solution which does work:

DELETE * FROM yourtable
WHERE ClientID NOT IN
(SELECT ClientID FROM LinkedExcelTable);

That is all well and good BUT I am still very curious to know why your
solution won't work. Would it have anything to do with the fact that
the Access table I am trying to delete from is also a linked table?
Don't ask me WHAT it is linked to! I haven't figured that one out yet!

Huh. Seems I gave Jet SQL too much credit. It looks as though this SQL
dialect doesn't support a joined-table expression in the FROM clause.
Such an expression is valid in other SQL dialects. That was my mistake;
I'm sorry to have led you astray. I was trying to avoid the "NOT IN"
syntax, because Jet SQL doesn't handle it very efficiently. Another
alternative is something like this:

DELETE ClientServices.* FROM ClientServices
WHERE ClientServices.ClientID IN
(SELECT ClientServices.ClientID
FROM ClientServices
LEFT JOIN Keepers
ON ClientServices.ClientID = Keepers.ClientID
WHERE Keepers.ClientID Is Null);

But if your problem has been well solved with the simple NOT IN
expression, then there's no need to pursue it any farther. Again, I'm
sorry to have given you faulty advice in the first place.