First Occurrence of Record by Date

Question

I am trying to return an Employee Name and Award Date if they have no prior award dates before dates the user specifies (these are fields on a form, StartDateTxt and EndDateTxt as seen below), aka their "first occurrence."

Example (AwardTbl only for simplicity):

AwardDate   EmployeeID  PlanID   AwardedUnits
3/1/2005    100200         1          3
3/1/2008    100200         1          7
3/1/2005    100300         1          5
3/1/2013    100300         1          8

If I ran the query between the dates 1/1/2005 - 12/31/2005, it would return 3/1/2005 and 100200 and 100300. If I ran the query between 1/1/2008-12/31/2008 it would return nothing and likewise with 1/1/2013 - 12/31/2013 because those employees have already had an earlier award date.

I tried a couple different things, which gave me some weird results.

SELECT x.AstFirstName ,
       x.AstLastName ,
       y.AwardDate ,
       y.AwardUnits ,
       z.PlanDesc
FROM (AssociateTbl AS x
      INNER JOIN AwardTbl AS y ON x.EmployeeID = y.EmployeeID)
INNER JOIN PlanTbl AS z ON y.PlanID = z.PlanID
WHERE y.AwardDate BETWEEN [Forms]![PlanFrm]![ReportSelectSbfrm].[Form]![StartDateTxt] And [Forms]![PlanFrm]![ReportSelectSbfrm].[Form]![EndDateTxt] ;

This query did NOT care if there was previous record or not, and I think that's where I am unsure of how to narrow down the query.

I also tried :

1. Min(AwardDate) (didn't work)
2. A subquery in the WHERE clause that ordered by TOP 1 AwardDate ASC, which only returned 1 record
3. A DCount("*", "AwardTbl", "AwardDate < [Forms]![PlanFrm]![ReportSelectSbfrm].[Form]![StartDateTxt]") < 1 (This also did not differentiate whether or not it was the first occurrence of the AwardDate)

Please note: This is MS Access. There is no ROW_NUMBER() or CTE features.

Answer 1

Try like below

SELECT x.AstFirstName ,
       x.AstLastName ,
       y.AwardDate ,
       y.AwardUnits ,
       z.PlanDesc
FROM (AssociateTbl AS x
INNER JOIN AwardTbl AS y 
ON x.EmployeeID = y.EmployeeID)
INNER JOIN PlanTbl AS z 
ON y.PlanID = z.PlanID
WHERE y.AwardDate BETWEEN '1/1/2005' AND '12/31/2005'
GROUP BY y.EmployeeID,
       x.AstFirstName ,
       x.AstLastName ,
       y.AwardDate ,
       y.AwardUnits ,
       z.PlanDesc
HAVING COUNT(*) = 1

Answer 2

I believe you need a nested select. The nested query will select with Min(awardDate), and the outer query will filter for the time period you want.

If you have both in the same query, it'll filter out the results before it does Min(awardDate) returning you the Min(awardDate) in your specified time.

select y.employeeId, y.awardDate from (
  select x.employeeId, min(x.awardDate) as awardDate 
    from AssociateTbl as x 
    group by x.employeeId) as y 
where y.awardDate >= :startAwardDate and y.awardDate < :endAwardDate;

Answer 3

Min Award Date will work if grouped by EmployeeID with your date range in the Having clause and used as a filter list:

SELECT x.AstFirstName ,
       x.AstLastName ,
       y.AwardDate ,
       y.AwardUnits ,
       z.PlanDesc
FROM  ((AssociateTbl AS x
    INNER JOIN AwardTbl AS y ON x.EmployeeID = y.EmployeeID)
    INNER JOIN (select a.EmployeeID,min(a.AwardDate) AS MinAwardDate
                from AwardTbl AS a
                group by a.EmployeeID
                having ((min(a.awardDate)>=[Forms]![PlanFrm]![ReportSelectSbfrm].[Form]![StartDateTxt]
                    and min(a.awardDate)<[Forms]![PlanFrm]![ReportSelectSbfrm].[Form]![EndDateTxt]))
               ) AS d on d.EmployeeID = x.EmployeeID
                   and d.MinAwardDate = y.AwardDate)
    INNER JOIN PlanTbl AS z ON y.PlanID = z.PlanID