Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1

    Question Unanswered: Can you join a table to an expression created from another table?

    I have 2 tables that I want to join in an Access query. Normally you just click and drag to join the fields, but that won't work in this instance. In the query design window, I created an expression from one of the fields in Table1 (Exp1: Right(someField,5)) which is displaying the last 5 characters of someField. I then add another table (Table2) to the query design, and I want to join Exp1 to a field in Table2. Any way to do this?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's an example. We have two Tables: [Tbl_CF_Data] and [Tbl_LCF_Data] and I want to join them on: the first two char. in Tbl_LCF_Data.Price_Number matching Tbl_CF_Data.Customer:
    Code:
    SELECT Tbl_LCF_Data.Price_Number, Tbl_CF_Data.Customer
    FROM Tbl_LCF_Data INNER JOIN 
         Tbl_CF_Data ON Tbl_CF_Data.Customer=Left(Tbl_LCF_Data.Price_number,2);
    You'll have to type it in SQL view because such joins cannot be represented in Design view.
    Have a nice day!

  3. #3
    Join Date
    Oct 2010
    Location
    Atlanta, GA
    Posts
    211
    Provided Answers: 1
    ah, that's what i figured. i was fooling around in the SQL view but couldn't get the right syntax. Thanks.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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