ACCESS Query ? Multiple Counts Duplicates Sum

[pat731]

I have two different tables and link them together to get
something that looks like this



ssn act code ppe emst
333-33-3333 02 33 1
333-33-3333 06 31 1
333-33-3333 09 1 1
444-33-3333 02 33 2
444-33-3333 06 32 2
444-33-3333 02 6 2


So what I need is the Sum of the ppe by SSN AND THEN KEEPING THE HIGHEST ACT CODE - BY THE HIGHEST ORGINAL PPE.
Can someone

 

[swampBoogie]

What do you mean by

BY THE HIGHEST ORGINAL PPE

?

What result (and why) would you expect from the given sample?

 

[pat731]

the result i would need would be

333-33-3333 02 65 1


444-33-3333 02 71 2
I need to count the ssn by their activity code with the Highest PPE. Then sum the ppe in all activity codes;
does that help

 

[swampBoogie]

Hmm

select ssn,(select actcode from t as t2 where ssn = t1.ssn
               and ppe = (select max(ppe) from t
                where ssn = t2.ssn) as MaxActCode,
       sum(ppe),emst from t as t1
 group by ssn,emst

I'm not sure that this what you want but maybe you can elaborate from here on.

 

[pat731]

can I do that from my querie view or should i do it with the sql view. thanks I am still learning