Use database to remove data from another?

[James_]

Hello,



I'm new to Access so please forgive if I seem completely inept.

I want to use one database to remove data from another e.g. use a list of telephone numbers to remove remove any entry with one of those telephone numbers in another database.
I thought this would be fairly simple but cannot find any info on the web. Please let me know if you need any more info.

Thanks in advance,
James.

[Robeen]

One option you could consider would be to export your list of telephone numbers from one database [to an Excel spreadsheet - if the list of numbers is less than 65,000 records] - and then - in the other database import the spreadsheet with the numbers into a new table.

Then you will have your phone numbers in the database that you need them in and you can work with them there.

Let me know if you need more help.

There are automated wizards in Access that will step you through doing most of this stuff - and you can Google 'ms access export/import spreadsheet' - but feel free to ask.

[James_]

Thanks for the reply. If I import the numbers onto the same table can I then automatically remove records with numbers that match? It needs to be automated as there's about 300k records.

[Robeen]

You should import your numbers into a new table.
Then - you should Create a Query like this:
1. Choose the table that has records that you want to delete.
2. Choose the new table that you created while importing your telephone numbers from the other database.
3. Join the telephone numbers in the two tables by dragging the telephone number field in one table to the telephone number field in the other table.
4. Select all the fields in your first table and put them into the grid in the lower half of the query design window.
When you run this query - it should give you all the rows in the original table that have telephone #s matching the ones you imported.
Once you are CERTAIN that the query is giving you the correct rows of data - and that you DO want those rows deleted . . .
5. With the query open in design view - Convert the query into a Delete query [by clicking the big red X button at the top of the query designer].
This will make the query a Delete query.
6. Again - if you are certain that the rows the query displayed ARE the ones you wanted to delete . . .
7. Run the Delete query and it will delete all those rows from your original table. Note: you cannot undo the delete. Those records will be permanently deleted.

If you have any doubts - create a copy of the table before you run the delete query that you created.

I hope this helps!

[James_]

Thanks for the reply Robeen. It looks like it will work great, the only problem I'm having is that when I change it to a delete query I get the message

'Specify the table containing the records you want to delete.'

Any advice would be great.

EDIT: Here is the code,

DELETE Sheet1.[Full Name], Sheet1.Line1, Sheet1.Line2, Sheet1.Line3, Sheet1.Line4, Sheet1.Phone
FROM Sheet2 INNER JOIN Sheet1 ON Sheet2.Phone = Sheet1.Phone;

[Robeen]

Sorry James!

Doing it my way would have given you SQL that looked something like this:

 
DELETE Table4.CustomerName, Table4.CountLocation, Table4_Copy.CustomerName
FROM Table4 LEFT JOIN Table4_Copy ON Table4.[CustomerName] = Table4_Copy.[CustomerName]
WHERE (((Table4_Copy.CustomerName) Is Null));

What you need is something like this:

 
DELETE *
FROM Table4
WHERE [Table4].[CustomerName] IN
(Select [Table4_Copy].[CustomerName] FROM [Table4_Copy]);

In my test db - I tried to re-create your scenario on a small scale.
I created Table4 with 5 records in it.
Then I created a copy of it named Table4_Copy.
I deleted one of the records from the copy.
When I run with the modified SQL - I get the standard Access message asking if I really want to delete 4 records . . .

See if you can go into the SQL of your query [the one that is asking you to specify the table name . . .] and change it following my example.
In query design mode - click 'View' in the top left corner and then cllick 'SQL View'.

I hope this helps!

Again - sorry for the little goof up!
[I learned something here too].

[Robeen]

I just looked over your last post and saw you had your SQL in there.

Change this:

 
DELETE Sheet1.[Full Name], Sheet1.Line1, Sheet1.Line2, Sheet1.Line3, Sheet1.Line4, Sheet1.Phone
FROM Sheet2 INNER JOIN Sheet1 ON Sheet2.Phone = Sheet1.Phone;

To:

 
DELETE * FROM Sheet1 
WHERE Sheet1.Phone IN
(SELECT Sheet2.Phone FROM Sheet2);

Let me know if that works for you!

[James_]

Works perfect

Thanks so much for your help.

[Robeen]

Happy to help!