Phone Number Formats

[clarko2122]

I have two different tables in an Access (2003) database file - each table has over 100,000 records. I need to compare and find duplicates within two different tables. The problem is that one table has the phone number formatted and appearing as "123 456-7890" and the other table has the phone number formatted as "123-456-7890". I can't create a "Find duplicates" query with the different phone number formats and I would like to format the numbers so there is a uniform display between the two tables.

Can anyone help?

Thanks in advance.

[Slope]

Use Replace() to get rid of all spaces and dashes so you are comparing like values.

[clarko2122]

Is it possible to add that expression to an SQL Union Query? I can't seem to get it to work, keeps returning a syntax error.

Any help would be appreciated!

[Slope]

Yes. Post your query so we can take a look.

[clarko2122]

Here is my union query that I would like to add the Replace function to.
Thanks again for all the help, I really appreciate it!


SELECT ORIG_NUM, DIALED_800, AT_DESC, CALL_DATE, CNCT_TIME, DURATION, FR_PLACE_S, FR_PLACE, RP_DESC, TERM_NUM, USG_AMT
FROM [01-07 Dup Calls];
UNION ALL
SELECT ORIG_NUM, DIALED_800, AT_DESC, CALL_DATE, CNCT_TIME, DURATION, FR_PLACE_S, FR_PLACE, RP_DESC, TERM_NUM, USG_AMT
FROM [02-07 Dup Calls];
UNION ALL SELECT ORIG_NUM, DIALED_800, AT_DESC, CALL_DATE, CNCT_TIME, DURATION, FR_PLACE_S, FR_PLACE, RP_DESC, TERM_NUM, USG_AMT
FROM [03-07 Dup Calls];

[Slope]

Assuming ORIG_NUM is your phone number column, try:

SELECT 
    REPLACE(REPLACE(x.ORIG_NUM, "-", ""), " ", "") AS ORIG_NUM,
    x.DIALED_800, 
    x.AT_DESC, 
    x.CALL_DATE, 
    x.CNCT_TIME, 
    x.DURATION, 
    x.FR_PLACE_S, 
    x.FR_PLACE, 
    x.RP_DESC, 
    x.TERM_NUM, 
    x.USG_AMT
FROM
(
SELECT ORIG_NUM, DIALED_800, AT_DESC, CALL_DATE, CNCT_TIME, DURATION, FR_PLACE_S, FR_PLACE, RP_DESC, TERM_NUM, USG_AMT FROM [01-07 Dup Calls] UNION ALL 
SELECT ORIG_NUM, DIALED_800, AT_DESC, CALL_DATE, CNCT_TIME, DURATION, FR_PLACE_S, FR_PLACE, RP_DESC, TERM_NUM, USG_AMT FROM [02-07 Dup Calls] UNION ALL 
SELECT ORIG_NUM, DIALED_800, AT_DESC, CALL_DATE, CNCT_TIME, DURATION, FR_PLACE_S, FR_PLACE, RP_DESC, TERM_NUM, USG_AMT FROM [03-07 Dup Calls]
) AS x