Forgot a couple of little things!!

SELECT T1.[Item codes], SUM(T1.[qty units]) AS TotalUnits, MAX(P.Prices) AS
PricesMaxDate
FROM MyTable T1
INNER JOIN (
SELECT T2.[Item codes], MAX(T2.[prices]) AS Prices
FROM MyTable T2
WHERE T2.[proce dates] = (
SELECT MAX(T3.[proce dates])
FROM MyTable T3
WHERE T3.[Item codes] = T2.[Item codes])
GROUP BY T2[Item codes]) P ON P.[Item codes] = T1.[Item codes]
GROUP BY T1.[Item codes]


"Paul" <pchronos@hotmail.com> wrote in message
news:a8a58f97.0308071245.44550be8@posting.google.c om...
> I have a simple table that contains Item codes, qty units, prices and
> proce dates as follows:
>
> ItemA, 1, 7.75, 2003/08/03
> ItemA, 2, 9.22, 2003/08/04
> ItemA, 5, 7.25, 2003/08/02
> ItemA, 4, 8.25, 2003/08/01
> ItemB, 3, 3.35, 2003/08/03
> ItemB, 9, 3.25, 2003/08/05
> ItemC, 3, 0.98, 2003/08/07
> ItemC, 7, 0.78, 2003/08/03
> ItemC, 4, 0.48, 2003/08/02
> etc..
>
> I am trying to write a query that will return one line per item (
> GROUP BY Item ) with the sum of units for each group and the price of
> the MAX date for each group.
>
> ex: For the above the result should be be:
>
> ItemA, 12.00, 9.22
> ItemB, 12.00, 3.25
> ItemC, 14.00, 0.98
>
> Thanks.