Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2002
    Location
    Maine
    Posts
    49
    Provided Answers: 3

    Answered: INNER JOIN woes....

    Hello,

    I'm using Access 2013 with linked tables to an SQL server. I have 2 tables, Office Locations, and UserInfo. There are users, associated with multiple offices in the Office locations table. But, they have different titles. The fields in the Office Locations table is just an ID for a lookup into the UserInfo table. I am able to do an INNER JOIN for one of the user titles, and get a real name, but how do I do this with more than one field? For example, the below query works fine. But as soon as I try to use AND to add another INNER JOIN clause, it won't work.

    SELECT UserInfo.Name, UserInfo.ID, [Office Locations].[Server Support].Value, [Office Locations].IT_Region, [Office Locations].[Desktop Support].Value, [Office Locations].[Office Name], [Office Locations].[Site Code], [Office Locations].[IT Helper].Value, [Office Locations].RITC
    FROM UserInfo INNER JOIN [Office Locations] ON UserInfo.ID = [Office Locations].[Server Support].Value
    WHERE ((([Office Locations].[Server Support].Value)=2337));


    I want real names for Desktop Support, IT Helpers, and RITC. The only real name I can get returned is Server Support, or just any one field I should say.
    Also to note, Server Support, and Desktop Support, are multiple valued fields. But I only care about the first name listed, and the .Value seems to work.

    Any help would be great! Thanks.

  2. Best Answer
    Posted by pbaldy

    "In design view, add the locations table 3 times. Access will alias 2 of them, adding _x to the end (you can change the alias name). Join each of your 3 fields to a different copy of the locations table."


  3. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    In design view, add the locations table 3 times. Access will alias 2 of them, adding _x to the end (you can change the alias name). Join each of your 3 fields to a different copy of the locations table.
    Paul

  4. #3
    Join Date
    Oct 2002
    Location
    Maine
    Posts
    49
    Provided Answers: 3
    OMG! Thank you so much! That was it! Been banging my head on the wall for a solid day over that.

  5. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Happy to help!
    Paul

Posting Permissions

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