Results 1 to 5 of 5

Thread: Query situation

  1. #1
    Join Date
    Aug 2004
    Posts
    9

    Unanswered: Query situation

    I'm trying to run a query based on a table with over 1 million records. The field that I'm basing this query on in my table is a field that contains a 10-digit telephone number. The telephone numbers in the field look exactly like this - 4075554623'

    In another table, I have a field that contains only an area code and prefixes. The records look exactly like this - 407555 *

    What I'm wanting to do is run a query that pulls up phone numbers from my original table that match only the area codes and prefixes listed in my other table. I hope this makes sense. If not, please post any other details that you might need. Thank you very much.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    First off, if you have mover a million records, you should SERIOUSLY think about upgrading your platform.

    That said:

    SELECT *
    FROM t1 INNER JOIN t2 ON t1.number = LEFT$(t2.number, 5)

    This assuming t1 is your original table, and t2 is the table with just the area code + prefix.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Aug 2004
    Posts
    9
    I keep getting the infamous syntax error in date error or missing operator error. Here's my syntax....


    SELECT *
    FROM CDRs_090904 INNER JOIN Cingular_NPANXX ON CDRs_090904.Calling# = LEFT$(Cingular_NPANXX.NPANXX, 5);

    t1 = CDRs_090904 which is my original table
    t2 = Cingular_NPANXX which is the table that includes only area codes and prefixes
    Calling# = the table that holds the phone numbers
    NPANXX = the table that holds the area codes+prefixes.

  4. #4
    Join Date
    Aug 2004
    Posts
    9
    CORRECTION.......

    I keep getting the infamous syntax error in date error or missing operator error. Here's my syntax....


    SELECT *
    FROM CDRs_090904 INNER JOIN Cingular_NPANXX ON CDRs_090904.Calling# = LEFT$(Cingular_NPANXX.NPANXX, 5);

    t1 = CDRs_090904 which is my original table
    t2 = Cingular_NPANXX which is the table that includes only area codes and prefixes
    Calling# = the FIELD that holds the phone numbers in table CDRs_090904.
    NPANXX = the FIELD that holds the area codes+prefixes in table Cingular_NPANXX.

  5. #5
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    First, I think date error appears because you use # sign in your query. that character used to specify date values like #1/1/2204#. so you have to take Calling# field into brackets whenever use it in a query as [Calling#].

    Second I think you should truncate the Table1, not Table2. since full numbers in Table1.

    Code:
     
    SELECT * 
    FROM CDRs_090904 INNER JOIN Cingular_NPANXX ON LEFT$(CDRs_090904.[Calling#],5) = Cingular_NPANXX.NPANXX
    ghozy.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •