Top Date for Each Employee Record (1 Viewer)

[GrandMasterTuck]

Hi folks. I have a query that pulls a list of employees that have at least one call-in-sick date from tblEmployeeSchedules. The trouble I'm having is that this query shows me ALL call-in-sick dates for all employees, and I just want the MOST RECENT call-in-sick date for each employee.

I tried a SELECT TOP 1 type query, but then I only get the one employee that called in sick most recently. I'm pretty sure I have to do this with some kind of subquery, but my programming chops are minimal at best.

tblEmployeeSchedules

EmpName     SchDate      Worked?    Loca
-------------------------------------------------
Jason        8/1/16        Y        Yard
Jason        8/2/16        Y        Cube
Jason        8/3/16        Y        Trans
Bill         8/1/16        Y        Cube
Bill         8/2/16        N        Trans
Bill         8/3/16        Y        Yard
Cindy        8/1/16        N        Trans
Cindy        8/2/16        Y        Yard
Cindy        8/3/16        N        Cube

When I use SELECT TOP 1, I only get Cindy's record from 8/3. If I do it without the TOP 1 part, I get two records for Cindy, because she called in twice, but I only want ONE of them, the one with the most recent date. I also tried putting TOP(SchDate) in the SELECT statement but I got an error about not recognizing TOP.

What I want to see is ONE record for Bill (the 8/2 record) and ONE record for Cindy (the 8/3 record). Cindy's 8/1 record should not be on there (because the 8/3 record is) and Jason should not be on it at all (because he never called in).

I know this is probably simple, and I'm just not getting the syntax right or something like that. Any ideas?

 

[Minty]

You might need a sub query , You need to group by employee, and not bring in location.
Create each query in the designer then add the SQL to the main query as the sub query as per Allen Brownes site http://allenbrowne.com/subquery-01.html

 

[plog]

I just want the MOST RECENT call-in-sick date for each employee.

No, you don't need a subquery for that. What you need to do is a totals query (use the Sigma/Summation symbol in the ribbon). You then bring in just the fields you need, in the way that you need them.

You want every person, so you bring down 'EmpName' into the query. This field should be GROUP BY because you want a record for each.

You want the most recent date, so you bring down SchDate. Because you want just the most recent you select 'Max' for that field.

You need to filter it just to absent days, soy ou need Worked?. This field is just criteria so it shouldn't show in your results, you simply use it to filter. Change it to Where and place your criteria underneath it.

Run that and you have your data.

 

[GrandMasterTuck]

Bam, plog, that was the solution. Thanks again!