merge 2 columns of data

[Guest]

Hi all,

Using access 2002. Importing a text file that contains 4 million phone
numbers. The text file shows area code then a comma and then phone
number(123,4567890). When importing the records into the database access
creates seperate columns for area code and phone number. How can I put the
area code and phone number in one column in the database? Also, I have a
different database of 1200 phone numbers. I need assistance in trying to
compare the two databases so I can remove duplicate numbers from the smaller
database.

 

[Guest]

Hi.

How can I put the
area code and phone number in one column in the database?

The quickest way to do this is to create the table first, then make sure
that there are "headers" or column names in the text file. Then create and
run a query such as the following:

INSERT INTO tblTextImport ( PhoneNum )
SELECT Code & Phone AS PhoneNum
FROM [TEXT;DATABASE=C:\Work\Test\].TextImport.txt;

.... where the table name is tblTextImport, PhoneNum is the name of field
with the area code and phone number, Code is the column in the text file with
the area code, Phone is the column in the text file with the phone number,
and C:\Work\Test\TextImport.txt is the name and path of the text file to
import the phone numbers from.

I need assistance in trying to
compare the two databases so I can remove duplicate numbers from the smaller
database.

Once you have the phone numbers imported into the database, use the Link
Table Manager to link to the table of phone numbers in the other database.
Use the "Find Duplicates" Query Wizard to create a query that identifies
duplicates for you. Delete the duplicates once found.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

 

[Guest]

Thanks for the help. I'll give it a try tonite when I get home from work.

Also cool name, love those old comaros.

Hi.

How can I put the
area code and phone number in one column in the database?

The quickest way to do this is to create the table first, then make sure
that there are "headers" or column names in the text file. Then create and
run a query such as the following:

INSERT INTO tblTextImport ( PhoneNum )
SELECT Code & Phone AS PhoneNum
FROM [TEXT;DATABASE=C:\Work\Test\].TextImport.txt;

... where the table name is tblTextImport, PhoneNum is the name of field
with the area code and phone number, Code is the column in the text file with
the area code, Phone is the column in the text file with the phone number,
and C:\Work\Test\TextImport.txt is the name and path of the text file to
import the phone numbers from.

I need assistance in trying to
compare the two databases so I can remove duplicate numbers from the smaller
database.

Once you have the phone numbers imported into the database, use the Link
Table Manager to link to the table of phone numbers in the other database.
Use the "Find Duplicates" Query Wizard to create a query that identifies
duplicates for you. Delete the duplicates once found.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

Hi all,

Using access 2002. Importing a text file that contains 4 million phone
numbers. The text file shows area code then a comma and then phone
number(123,4567890). When importing the records into the database access
creates seperate columns for area code and phone number. How can I put the
area code and phone number in one column in the database? Also, I have a
different database of 1200 phone numbers. I need assistance in trying to
compare the two databases so I can remove duplicate numbers from the smaller
database.

 

[Guest]

Used your instructions to create a table to insert area code and phone number
into one column in a new table. It worked GREAT. However, still having
problems with finding duplicate records.

I have two tables; one with 4 million phone numbers and one with 2700 phone
numbers. I'm trying to compare the smaller table to the bigger table and
suppress any phone numbers form the smaller table that are listed in the
bigger table. I don't want to merge them together because I'm using the
smaller table for a mailing. Could you help me again in trying to accomplish
this

Thanks,
jskamm

Hi.

How can I put the
area code and phone number in one column in the database?

The quickest way to do this is to create the table first, then make sure
that there are "headers" or column names in the text file. Then create and
run a query such as the following:

INSERT INTO tblTextImport ( PhoneNum )
SELECT Code & Phone AS PhoneNum
FROM [TEXT;DATABASE=C:\Work\Test\].TextImport.txt;

... where the table name is tblTextImport, PhoneNum is the name of field
with the area code and phone number, Code is the column in the text file with
the area code, Phone is the column in the text file with the phone number,
and C:\Work\Test\TextImport.txt is the name and path of the text file to
import the phone numbers from.

I need assistance in trying to
compare the two databases so I can remove duplicate numbers from the smaller
database.

Once you have the phone numbers imported into the database, use the Link
Table Manager to link to the table of phone numbers in the other database.
Use the "Find Duplicates" Query Wizard to create a query that identifies
duplicates for you. Delete the duplicates once found.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

Hi all,

Using access 2002. Importing a text file that contains 4 million phone
numbers. The text file shows area code then a comma and then phone
number(123,4567890). When importing the records into the database access
creates seperate columns for area code and phone number. How can I put the
area code and phone number in one column in the database? Also, I have a
different database of 1200 phone numbers. I need assistance in trying to
compare the two databases so I can remove duplicate numbers from the smaller
database.

 

[John Vinson]

I have two tables; one with 4 million phone numbers and one with 2700 phone
numbers. I'm trying to compare the smaller table to the bigger table and
suppress any phone numbers form the smaller table that are listed in the
bigger table.

The Unmatched Query Wizard will set this up for you. In SQL it would
be

SELECT <whatever>
FROM Smalltable
LEFT JOIN Bigtable
ON Smalltable.Phone = Bigtable.Phone
WHERE Bigtable.Phone IS NULL;

This "frustrated outer join" query find only those records in
Smalltable which do NOT have a match in Bigtable.

John W. Vinson[MVP]