Access SQL: Pull Related Information From Another Table Alongside Group By -

this question involves following code:

select a.`fsr name` [last name],      min(a.`actual tep in`) [date in],      max(b.site) site,      max(b.position) position,     max(b.comments) comments deployments inner join deployments b on a.id = b.id a.`actual tep in` > now() group a.`fsr name`; 

the point of code query first date beyond today each person, , pull 1 record. example there can many smiths dates before , after today, , want show record smith after today. idea show each person's next date on cascading list. @ least part, code works.

the problem having information needs come alongside it. group forces aggregates keep pulling wrong site/position/comments fields when min or max them. i'm not sure should doing @ point seems i'm bit on head. appreciated!

note: i'm doing in access 2013.

edit: adding rough table

fsr name    date in     site    position    comments bob         7/3/2014      op          notes joe         10/11/2013  b   op   joe         8/4/2014    c   op          sea joe         9/1/2014    d   op          dep buckley     11/1/2012     op          transfer buckley     10/7/2012   b   op   jones       6/10/2012     op   jones       4/27/2013     op          3 notes jones       6/21/2011   b   op   jones       3/26/2012   b   op          5 notes jones       6/10/2012   c   op          1 notes jones       3/23/2014   c   op          2 notes jones       7/5/2014    d   op          dep calvillo    2/21/2014     op   calvillo    10/3/2014   b   op          com calvillo    11/10/2014    op          dep casey       5/2/2014    c   op   

select d1.[fsr name], first(d2.[date in]), ... deployments d1       left join (select * deployments d3 d3.[date in] > now() order d3.[date in]) d2          on d1.[fsr name] = d2.[fsr name] group d1.[fsr name]; 

solution description: joining ordered list , first() return values same row. first() aggregate function exists in access, other database engines have similar ones or can deal without it.