Macro Help

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

Guest

Help! I'm a new ACCESS user and I'm trying to filter and delete records that contain matching phone numbers from a table of 3.5 million numbers

Basically, I have 3 tables. 1 table contains the phone numbers that are the qualifier, so if a record on one of the other 2 tables has that matching phone number, then I want to delete that record or move it to another table thereby removing it from the table that it is on

Now, the other issue I have is that the phone number is broken up over 2 columns: one column is the area code and the other is the phone number

Can anybody at least point me in the right direction? I have no experience creating macros in access or excel

THANKS!
 
Take a look a Visual Basic, specifically arrays, loops,
and control structures like If, then, else statements.

Depending on the data, I might be inclined to load the
numbers into an array, then search for and remove matching
numbers based on certain criteria, such as how do you know
what number is a keeper?

Bob
-----Original Message-----
Help! I'm a new ACCESS user and I'm trying to filter and
Click to expand...
delete records that contain matching phone numbers from a
table of 3.5 million numbers.
Basically, I have 3 tables. 1 table contains the phone
Click to expand...
numbers that are the qualifier, so if a record on one of
the other 2 tables has that matching phone number, then I
want to delete that record or move it to another table
thereby removing it from the table that it is on.
Now, the other issue I have is that the phone number is
Click to expand...
broken up over 2 columns: one column is the area code and
the other is the phone number.
Can anybody at least point me in the right direction? I
Click to expand...
have no experience creating macros in access or excel.
 
I don't know anything about the visual array basic..

what I want to do is

Find all records on table A & B that have a matching phone number with a record on Table C and I want to either a) delete the record or B) move the record to another table - whichever is easier

The additional issue I'm having is that the area code and phone number are 2 seperate fields (Columns

I have literally no programming experience....so this seemingly simple task is a chore for me

Thanks in advance!
 
Fred said:
I don't know anything about the visual array basic...

what I want to do is:

Find all records on table A & B that have a matching phone number
with a record on Table C and I want to either a) delete the record or
B) move the record to another table - whichever is easier.

The additional issue I'm having is that the area code and phone
number are 2 seperate fields (Columns)

I have literally no programming experience....so this seemingly
simple task is a chore for me.

Thanks in advance!
Click to expand...

Assuming that the area code and phone number fields in all these tables
are text fields, and that the values in them are formatted the same way
(e.g., either there's no hyphen in any of the phone-number fields, or
there's a hyphen in all of the fields), then ...

To delete the records in A that are matched in C, create and run a query
with this SQL:

DELETE * FROM A
WHERE [AreaCode] & [PhoneNo]
IN (SELECT AreaCode & PhoneNo FROM C);

Substitute your own table names and field names in the above.

To copy all matching records from A into an existing table D with the
same fields:

INSERT INTO D
SELECT * FROM A
WHERE [AreaCode] & [PhoneNo]
IN (SELECT AreaCode & PhoneNo FROM C);

To "move matching records" from A to D, run the second, "INSERT" query
first, and then the first, "DELETE" query.

Do the same thing all over again for table B.
 
Back
Top