Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2017
    Posts
    1

    Unanswered: Query based on dates

    HI

    I have these 2 tables below in access and I am trying to update the PHONE_ID in Table1_Employees based on what is entered in Table2_IDs.
    Table2_IDs shows a date range an Avaya ID was used by an Employee. Some end dates are null as it is still being currently used. How can I do a lookup to return the avaya id used based on the EMP_ID and NOM_DATE in Table1_Employees?

    Table1_Employees
    ID EMP_ID EMP NOM_DATE PHONE_ID
    1 1495 Employee 1 7/2/2011
    2 1495 Employee 1 4/6/2013
    3 1495 Employee 1 1/12/2017
    4 1582 Employee 2 12/22/2016
    5 1582 Employee 2 1/12/2017
    6 1626 Employee 3 7/1/2011
    7 1626 Employee 3 8/1/2012
    9 1626 Employee 3 1/12/2017
    10 1626 Employee 3 12/22/2016
    11 1752 Employee 4 2/7/2015
    12 1752 Employee 4 1/12/2017

    Table2_IDs
    ID SalesRepID EMP AvayaID Avaya_Start Avaya_End
    1 1495 Employee1 1843277 4/6/2013
    2 1495 Employee1 1843373 7/1/2011 4/6/2013
    3 1582 Employee2 1843306 7/1/2011
    4 1626 Employee3 1843209 7/1/2011 11/1/2011
    5 1626 Employee3 1843509 11/1/2011
    6 1752 Employee4 1843214 1/5/2014 12/22/2016
    7 1752 Employee4 1846598 12/22/2016

  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    Try:
    Code:
    UPDATE Table1_Employees AS T1 INNER JOIN  Table2_IDs AS T2
    ON T1.Emp_ID = T2.SalesRepID AND T1.Nom_Date = T2.AvayaStart
    SET T1.Phone_ID = T2.AvayaID;
    For reference, your table structure is terrible. You should not have duplicate records for employees in a table called "Table1_Employees", and you don't need to include the employee's name in any other table that includes the employee's ID. Read up on database normalisation.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

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