Results 1 to 4 of 4
  1. #1
    HornFrog is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    3

    Updating Field in tblA based on multiple criteria including date range

    I have two simplistic tables where I would like to update one field in TblYTD with a value from TblPrograms where the EMPLID from both tables match and the TblYTD.CHECK_DT is greater than or equal to the TblPrograms.EVENT_DT. I tried attaching the accdb, but I couldn't get it below the size limitation for attaching.

    TblPrograms - historical table containing the PROGRAM and an EVENT_DT an employee was enrolled in that program. Employee can have multiple enrollments in programs. Table could have over 500K records.

    EMPLID EVENT_DT PROGRAM
    123 11/15/2010 FLX
    123 03/15/2016 NCB
    123 05/31/2016 TBA
    345 11/01/2010 MSB
    567 01/01/2011 ABC
    567 05/15/2016 CBS

    TblYTD - table containing an employees check history with fields EMPLID and CHECK_DT. I would like to add the TblPrograms.PROGRAM to the employees check record as shown below. Table could contain over 1000K records.

    EMPLID CHECK_DT PROGRAM
    123 01/01/2016 FLX
    123 02/15/2016 FLX
    123 03/15/2016 NCB
    123 04/15/2016 NCB
    123 06/15/2016 TBA
    345 01/01/2016 MSB
    345 06/15/2016 MSB


    567 01/01/2015 ABC
    567 02/15/2016 ABC
    567 06/15/2016 CBS

    With the potential record size in each table, efficiency of solution would be paramount. Thanks in advance for any advice and direction.

  2. #2
    HornFrog is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    3

    Updating Field in tblA based on multiple criteria including date range

    The sample database has been attached.

    I have two simplistic tables where I would like to update one field in TblYTD with a value from TblPrograms where the EMPLID from both tables match and the TblYTD.CHECK_DT is greater than or equal to the TblPrograms.EVENT_DT. I tried attaching the accdb, but I couldn't get it below the size limitation for attaching.

    TblPrograms - historical table containing the PROGRAM and an EVENT_DT an employee was enrolled in that program. Employee can have multiple enrollments in programs. Table could have over 500K records.

    EMPLID EVENT_DTPROGRAM
    123 11/15/2010 FLX
    123 03/15/2016 NCB
    123 05/31/2016 TBA
    345 11/01/2010 MSB
    567 01/01/2011 ABC
    567 05/15/2016 CBS

    TblYTD - table containing an employees check history with fields EMPLID and CHECK_DT. I would like to add the TblPrograms.PROGRAM to the employees check record as shown below. Table could contain over 1000K records.

    EMPLIDCHECK_DTPROGRAM
    123 01/01/2016 FLX
    123 02/15/2016 FLX
    123 03/15/2016 NCB
    123 04/15/2016 NCB
    123 06/15/2016 TBA
    345 01/01/2016 MSB
    345 06/15/2016 MSB
    567 01/01/2015 ABC
    567 02/15/2016 ABC
    567 06/15/2016 CBS

    With the potential record size in each table, efficiency of solution would be paramount. Thanks in advance for any advice and direction.
    Attached Files Attached Files

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Try thiis maybe. It will update the values directly to the table. Make sure you have a good backup or test it on a test table.

    DoCmd.SetWarnings = False
    Docmd.RunSQL "UPDATE tblPrograms INNER JOIN tblYTD ON tblPrograms.EmpID = tblYTD.EmpID SET tblYTD.Program = [tblPrograms].[Program]
    WHERE (((tblYTD.Check_DT)>=[tblPrograms].[Event_DT]));"
    DoCmd.SetWarnings = True

  4. #4
    HornFrog is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Aug 2016
    Posts
    3
    Bulzie, thank you for this SQL UPDATE syntax. This works. I had tried something similar using just a Query SELECT but it wasn't pulling the desired programs.

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

Similar Threads

  1. Replies: 4
    Last Post: 04-25-2015, 04:17 PM
  2. Replies: 5
    Last Post: 11-30-2014, 08:12 PM
  3. Replies: 3
    Last Post: 07-18-2013, 04:21 AM
  4. Replies: 1
    Last Post: 06-17-2011, 12:59 AM
  5. Replies: 3
    Last Post: 09-29-2009, 07:08 AM

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