Results 1 to 6 of 6
  1. #1
    Coryjacques is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    16

    VLookup in SQL - Multiple Tables

    Hello, I have two tables (below are examples along with the desired outcome). I'm looking to VLookup across these tables for values that are present in the table it's pulling from and another table. I'm not sure how to go about doing this - lets say that all of the information I have for Supervisor is a name, which is unique, but I need to pull in the supervisor's EEID, Email, and Location. The supervisors are in the EEName column which I'm looking up in. There are expected to be some blanks as certain employees (the CEO) do not report in to anyone.




    tblPersonal
    EEName EEID Email
    Bob 123456 Bob@.com
    Stan 234567 stan@.com
    ken 345678 ken@.com
    lee 456789 lee@.com

    tblCompany
    EEName Supervisor Location
    Bob Lee MA
    Stan Bob CA
    Ken Lee EU
    Lee MA


    Desired Outcome
    Employee Employee ID Email Address Office Location Supervisor Name Supervisor Email Supervisor Office Location
    Bob 123456 Bob@.com MA Lee lee@.com MA
    Stan 234567 stan@.com CA Bob bob@.com MA
    Ken 345678 Ken@.com EU Lee Lee@.com MA
    Lee 456789 Lee@.com MA

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Access uses DLookup. VLookup is in Excel
    However you don't need it here.

    Create a query joining both tables by EE Name
    You may need to add tblcompany a second time to get the final column
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    Coryjacques is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    16
    I'm not sure how to do that - I've tried this by using ON personal.EEName = Company.Supervisor, but the output is off

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The problem is really that your data structure is poor.
    This will makes it a bit better ...but others may have better suggestions
    tblPersonal - make sure EEID is the primary key
    tblCompany - add a PK field e.g. CID and replace EEName with EEID as a foreign key field

    It can then be done in 2 queries. Both have outer joins to handle the null value

    Query1
    Click image for larger version. 

Name:	Query1.PNG 
Views:	13 
Size:	18.6 KB 
ID:	37278

    Query2
    Click image for larger version. 

Name:	Query2.PNG 
Views:	13 
Size:	20.8 KB 
ID:	37279

    Results
    Click image for larger version. 

Name:	Query2Results.PNG 
Views:	13 
Size:	12.7 KB 
ID:	37280

    Hope that makes sense. See attached DB

    NOTE: Another solution might be possible with a query and subquery but I didn't try that
    Attached Files Attached Files
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Coryjacques is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    16
    This helped - what I ended up doing was just duplicating and renaming the "Personal" sheet and doing another innerjoin. I didn't understand at first that that's what was meant above.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I tried various approaches. What I posted was the best solution I came up with.
    If you have something possibly better, please show us your solution.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 09-13-2017, 06:19 AM
  2. Replies: 2
    Last Post: 03-30-2015, 12:38 PM
  3. Replies: 1
    Last Post: 08-25-2012, 06:11 PM
  4. Replies: 10
    Last Post: 07-12-2011, 11:09 PM
  5. Replies: 4
    Last Post: 06-14-2011, 07:19 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums