I have a table with a process date. I have another table in which each record has a start date and an end date. I was wondering how to make a field in the table with the process date that would tie back to the other table based on the date range. I've tried using SQL in the lookup for the field and I could select options, but I want it to auto:
Code:
SELECT Week_ID, Week_Start, Week_End, FY_Month, FY_Quarter, LHW_All_Claims_IMP_tbl.[Process Date] FROM CI_Calender_FY2010, LHW_All_Claims_IMP_tbl WHERE (LHW_All_Claims_IMP_tbl.[Process Date] BETWEEN CI_Calender_FY2010.Week_Start AND CI_Calender_FY2010.Week_End);