SELECT Table1.DateBEG, Table1.DateEND, Table2.[HIRE DATE], Table2.Action, Table2.EffectiveDate, IIf([Table2].[HIRE DATE]<=[table1].[action] And [Table2].[Field4]<>"LOA",1,0) AS expr1 FROM Table1, Table2;
I have two Tables as you can see in the images below. One containing ranges, and the other containing an employee id, hire date, effective date, and action.
The idea is to have a counter that counts each range that the hire date fits in, but if there is an instance of an action with "LOA" then the counter stops counting. Only counting the ranges where an effective Date with action LOA isn't present.
My code above doesn't entirely work as it still includes date ranges which include LOA. In the table, the ranges which should count is the second only with the third and first not counting as the first is before the hire date and the thrid range contains an instance of LOA in the date range. If anyone can assist me, it would be much appreciated!
