How to select data based on (highest) value in a paticular column (SQL)

Hi all

A question from a SQL newbie

I have the following dataset in access

KOITNO KOCSU5 KOPCDT
2 20 20160428
2 22 20160429
2 24 20160519
3 12 20160514
3 13 20160515

Here I would like to write an sql so that I only get the line with the highest value in the KOPCDT (date) column (text format) grouped by KOITNO (Itemnumber) in my new dataset. In this case:

2 24 20160519
3 13 20160515

I have tried several Things but can't seem to get just right

Anyone with a quick solution?

Thanks in advance!

/JJ

Hi alansidman

Thanks for your reply

Unfortunately, this doesn't quite Work. Sure enough only the highest value of KOPCDT is shown... but the issue is then KOCSU5.

If I select 'Group by' on the KOCSU5 then I still get multiple lines... if I choose 'sum' it shows me the sumtotal of all lines (in the above example 20+22+24 = 66)
It looks right if I choose 'last' as criteria but I'm not entirely sure if this is right? Maybe you're able to advise me?

My SQL is now:
SELECT M3PRDDTA_MCHEAD.KOITNO, Max(M3PRDDTA_MCHEAD.KOPCDT) AS MaxOfKOPCDT, Last(M3PRDDTA_MCHEAD.KOCSU5) AS LastOfKOCSU5
FROM M3PRDDTA_MCHEAD
GROUP BY M3PRDDTA_MCHEAD.KOITNO;

BR

JJ