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.