Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jan 2003
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Linking tables with an expression (2000)

    I have a query with the following expression "Expr1: Right([INV_CM_NUM],6)". I want to link this query by "Expr1" to a table such that it links only the right 6 numbers and ignores the rest. Thanks.

    Wayne

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Linking tables with an expression (2000)

    Are you saying that the field in the table only has 6 characters and you want to link the table field to the expression in a query?
    Charlotte

  3. #3
    New Lounger
    Join Date
    Jan 2003
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking tables with an expression (2000)

    In the table there is a field with 7 charecters. The first of the 7 characters is a 0. I want to link it to anouther table and exclude the 0 in the link.

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Joining tables with an expression (2000)

    I think what you want is something like this. In Query Design view, add both tables to query. Instead of creating join line between tables, use SQL WHERE clause like this example:

    SELECT TABLE1.FIELD1, TABLE2.FIELD2, TABLE1.ZIP5
    FROM TABLE1, TABLE2
    WHERE TABLE1.ZIP5 = LEFT(<!t>[TABLE2]<!/t>![ZIP9],5)
    ORDER BY TABLE1.FIELD1, TABLE1.ZIP5

    In this example Table1 has a 5-digit zip code field, while Table2 has a 9-digit zip code field. The WHERE clause "joins" the two tables based on the 5 digit zip in Table1 = first 5 digits of 9 digit zip in Table2 as returned by LEFT function.

    HTH

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Joining tables with an expression (2000)

    One would hope that there are not too many records in these tables, for you have created a Cartesian Product.
    If not too many records you should be ok.

    <<In the table there is a field with 7 charecters. The first of the 7 characters is a 0. I want to link it to anouther table and exclude the 0 in the link. >>
    Why don't you delete the leading zero permanently, that way you can then use the whole field in the link, which would be more efficient.

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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