Multiple Dates

[Access Intern]

I am trying to figure out how to perform the following calculation:

I have two columns: Effective Date Field and Review Date Field. A person
has multiple entries under the Effective Date Field. I am trying to
determine if the person's review date field falls in between one of their
effect dates.

 

[KARL DEWEY]

Are you looking for something like these queries?
Intern_1 --
SELECT YourTable.EmpName, Max(YourTable.[Review Date]) AS [MaxOfReview Date]
FROM YourTable
GROUP BY YourTable.EmpName;

SELECT YourTable.EmpName, YourTable.[Effective Date], Intern_1.[MaxOfReview
Date] AS [Last Review Date], YourTable_1.[Effective Date] AS [Previous
Effective Date]
FROM (YourTable INNER JOIN Intern_1 ON YourTable.EmpName = Intern_1.EmpName)
LEFT JOIN YourTable AS YourTable_1 ON Intern_1.EmpName = YourTable_1.EmpName
WHERE (((Intern_1.[MaxOfReview Date]) Between [YourTable].[Effective Date]
And [YourTable_1].[Effective Date]) AND ((YourTable_1.[Effective
Date])<[YourTable].[Effective Date]))
ORDER BY YourTable.EmpName, YourTable.[Effective Date],
YourTable_1.[Effective Date];

 

[Access Intern]

Thanks, this helped!

Are you looking for something like these queries?
Intern_1 --
SELECT YourTable.EmpName, Max(YourTable.[Review Date]) AS [MaxOfReview Date]
FROM YourTable
GROUP BY YourTable.EmpName;

SELECT YourTable.EmpName, YourTable.[Effective Date], Intern_1.[MaxOfReview
Date] AS [Last Review Date], YourTable_1.[Effective Date] AS [Previous
Effective Date]
FROM (YourTable INNER JOIN Intern_1 ON YourTable.EmpName = Intern_1.EmpName)
LEFT JOIN YourTable AS YourTable_1 ON Intern_1.EmpName = YourTable_1.EmpName
WHERE (((Intern_1.[MaxOfReview Date]) Between [YourTable].[Effective Date]
And [YourTable_1].[Effective Date]) AND ((YourTable_1.[Effective
Date])<[YourTable].[Effective Date]))
ORDER BY YourTable.EmpName, YourTable.[Effective Date],
YourTable_1.[Effective Date];

--
Build a little, test a little.

I am trying to figure out how to perform the following calculation:

I have two columns: Effective Date Field and Review Date Field. A person
has multiple entries under the Effective Date Field. I am trying to
determine if the person's review date field falls in between one of their
effect dates.