Selecting the highest value

[whlinder]

I have two tables that I want to link based on matching criteria, while only picking the highest value in a field.

My fields are TDNR, COUN1, ORAC1, DSTC1, CARR1, MCAR1, CLSC1, FBTD1, FCNO1, PROV2, PROC2, CABIN in Table 1 and ORIG, DEST, CXR, F/C, EFF, DISC, FTNT, RUL, CUR, FARE, TRF, IND, CLASSCD, FTID, and CABCD in Table 2.

I would like to create a new table from these 2, matching ORAC1 to ORIG, DSTC1 to DEST, and CABIN to CABCD. I want to bring back all the fields in Table 1 and the F/C, RUL, CUR, FARE and CLASSCD from Table 2 using Left Join. (I can do everything up to here) This will bring back many records where all the Table 1 records are the same, but the Table 2 records are different. When this happens, I want to pick the record with the highest value in FARE. I've looked at the TOP function but can't seem to get it to work. Is there a way to do this?

Thanks in advance, I just found this site and it looks like there is lots of good info on here.

 

[Norie]

Have you looked at Totals?

 

[whlinder]

Not exactly sure what you mean, do you mean changing the 'Group By' to select the Max or Min or something? I've played with some of those but it hasn't yielded the results I am looking for.