Combine data rows on condition and delet if null

[RC Miranda]

Hi all of you aswome access gurus..
I`m finding myself migrating to access and learing all its useful features but i have something that is stomping me that might seem simple to some of you

I have 2 tables TblMTD and TblDuplicates_Temp but will focus on the second one (TblDuplicates_Temp)

This table is a result from a query that finds the duplicate accounts from the Rawdata in TblMTD

The TblDuplicates_Temp has the following colums

Emplnum,EmplName,Skill,Call_Center,Manager,Calls

note that
skill=what that employee sells
call center =where they are located

In this duplicate table the only difference in records is that sometimes and emplyee number appears twice but one of his records has calls and another one does not.

like this

Emplnum,EmplName,Skill,Call_Center,Calls,attempts, promo,stat
11153,Jon Do,internet,london,123,5,null,temp
11153,John Do,cellular,india,0,9,low,perm

What i would need is to COMBINE same employee number records but to KEEP ONLY the SKILL,CALLS,STAT (maybe others in the future)of the employee number with the most calls

like this (as per the data above.

Emplnum,EmplName,Skill,Call_Center,Calls,attempts, promo,stat
11153,Jon Do,cellular,london,123,9,null,temp


AND..... to delete all the records for THAT emploYee if there are no call (i do not want to keep those records if they have some emplyees have 2 records but no calls)

Thank you soooo much in advance...i am simply stuck at that point

[NeoPa]

I would start with ensuring that [TblDuplicates_Temp] has an [ID] field when it's created. That's easily done simply by specifying an AutoNumber field in the design.

Actually, wouldn't a better and simpler idea be to delete any non-qualifying records from this table beforehand (or just disqualify those records before adding them in when the duplicates are detected). With the data left in the table only that which you require the problem is simplified away no?