Update field by Date Range

[soldat452002]

Hello Guys,

I'm new to Access Queries so I'm lost; I want to Inner Join two tables by ID. The Criteria would be that if the tblStatus.Eff & Exp has a Date Range found within tblAffiliation.Eff & Exp then bring over the Affiliation to the tblStatus.Affiliation field.


Desired Result:

tblStatus.ID Status tblStatus.Eff tblStatus.Exp Affiliation
012344 TMUTUAL 11/1/2011 6/30/2016 LLC





tblStatus.ID Status tblStatus.Eff tblStatus.Exp Affiliation
012344 COMPLETE 1/1/2000 10/31/2011 Null
012344 TMUTUAL 11/1/2011 6/30/2016 Null


TblAffiliation.ID Affiliation TblAffiliation.Eff TblAffiliation.Exp
012344 AXA LLC 1/1/2010 4/30/2010
012344 NEW INC 5/1/2010 05/01/2016

[CJ_London]

cross posted here with answers

http://www.access-programmers.co.uk/...92#post1497592

@soldat - please reads this thread

http://www.excelguru.ca/content.php?184