ms access - SQL use MAX() function select field not in Group By -

i want find recent entry unique combination of 2 fields in table.

employee_number | cap_id | score | date_added 

i want find recent 'date_added' group of employee_number , cap_id show 'score' entered group. can group employee_number , cap_id row want in result, can't show score group determine distinct value.

if table looks like:

employee_number cap_id  score   date_added 96149             2       4     04/06/2015 96149             2       3     03/06/2015 

i want result be:

employee_number cap_id  score   date_added 96149             2       4     04/06/2015 

i can use:

select employee_number, cap_id, max(date_added) scores group employee_number, cap_id 

to correct result, need score too

skip group by, return row if no other row same employee_number , cap_id later date exists!

select employee_number, cap_id, score, date_added scores s1 not exists (select 1 scores s2                   s1.employee_number = s2.employee_number                     , s1.cap_id = s2.cap_id                     , s1.date_added < s2.date_added) 

it'll return both rows if there's tie!

edit - kind of explanation:

the sub-query looks row same employee_number , cap_id, later date_added value.

if no such row exists, row main select returned.

what select in sub-select of no importance, important thing if row exists or not exists. (that's select 1. can select whatever in sub-query, if doesn't matter.)