Removing Duplicates from a One to Many "Two-Table" - Microsoft Community

i've 2 tables associated in sql select query, , wish have 1 record each person display in string.  this sql dropdown combobox, , editing sql not need do.

the primary table dynamic people table, having names, etc.

the many table military history of these folks.  quite few of them have been in more 1 branch of service.

my quest utilize recent rank branch of service in string.  i not using dates or years in string itself; end year of enlistments filter only.

i tried couple different methods, 1 being summary sql approach.  the other subquery in(select...).  i cannot either work.

here design view , 2 tables:

[p_mltry_hst_prsn_] indexed duplicates ok.

there other tables, normalized, such rank , branch of service, however, thought end year more appropriate filter, here's sql:

select pd.pdid, p_mltry_rnk_hst.p_mltry_hst_rnk_, p_mltry_rnk_hst.p_mltry_hst_prsn_
pd left join p_mltry_rnk_hst on pd.pdid = p_mltry_rnk_hst.p_mltry_hst_prsn_
group pd.pdid, pd.pd_nm_l, pd.pd_nm_f, p_mltry_rnk_hst.p_mltry_hst_rnk_, p_mltry_rnk_hst.p_mltry_hst_prsn_
having (((pd.pd_nm_f) not null) , ((p_mltry_rnk_hst.p_mltry_hst_prsn_) not null))
order pd.pd_nm_l, pd.pd_nm_f;

however, still duplicate records people's multiple enlistments; i'm shooting "most recent"

please help.

(tom, please go easy on me; abbreviations)

moved from: office / access / windows other / office 2010

lol, think tom = me. ok, not complain it, ease of writing paraphrase fields.

you said want recent date based on history.enddate. don't think that's enough, since (i guess) there more 1 same date same person. in such case, want tiebreaker be? perhaps history.id?

assuming yes, solve intermediate query, like:

qrymaxdateperperson:

select personid, max(enddate)
history
group personid

then second query max(id):

qrymaxidperperson:

select history.personid, max(history.id) maxofid
qrymaxdateperperson inner join history on (qrymaxdateperperson.maxofenddate = history.enddate) , (qrymaxdateperperson.personid = history.personid)
group history.personid;

then in new query inner join query history table on id pick history.*.

if want, can done in single query, simplicity of above solution.

Office / Access / Microsoft Office Programming / Office 2010