Duplicate records ID'ed throu criteria

[Ernesto]

Hello all...need some help with this...

I have a table with the following data...

ID Date Description Price Cost
1 03/01/06 Item 1 10.5 5.5
1 03/05/06 Item 1 11.36 6.75
3 03/06/06 Item 2 9.25 3.69
3 04/15/06 Item 2 11.45 4.89
5 04/15/06 Item 3 9.45 2.25

I'd like to be able to get the lastest Description, Price, and Cost by
month...like this...

ID Date Description Price Cost
1 03/05/06 Item 1 11.36 6.75
3 04/15/06 Item 2 11.45 4.89
5 04/15/06 Item 3 9.45 2.25

I've tried the following:

SELECT table.ID, Max(tblPST_Changes.Date) AS MaxOfDate,
table.Description, table.Price, table.Cost
FROM table
GROUP BY table.ID, table.Description, table.Price, table.Cost

but when I run the above query it returns this...

ID Date Description Price Cost
1 03/01/06 Item 1 10.5 5.5
1 03/05/06 Item 1 11.36 6.75
3 03/06/06 Item 2 9.25 3.69
3 04/15/06 Item 2 11.45 4.89
5 04/15/06 Item 3 9.45 2.25

why is it returning everything in the table?

help would be greatly appreciated....

peace!

 

[kerry_ja]

Hi Ernesto,

It is returning more than what you want, because there are different
prices/costs. To just return one, try something more along the lines
of:
Create a query called LatestDateByID with SQL of:
SELECT table.ID, Max(table.Date) AS LatestDate
GROUP BY table.ID

Then your query would be:
SELECT table.ID, LatestDateByID.LatestDate, table.Description,
table.Price, table.Cost
FROM table, LatestDateByID
WHERE table.ID=LatestDateByID.ID

 

[Guest]

Try something like

SELECT M1.*
FROM TableName AS M1
WHERE M1.[Date] = (SELECT Max([Date]) As MaxDate
FROM TableName as M2
WHERE M2.ID=M1.ID)

If you have a field name [Date] consider changing it, it's better not to use
key words in Access

 

[Guest]

A couple of items noted -
Your table name should not be "table" as it is a reserved word in Access.
Is "tblPST_Changes" a query or another table? It is not joined with your
data table in any way.

 

[Ernesto]

Karl - "tblPST_Changes" is a table which I have made into a subform for
another table (to track multiple changes from one item; one-to-many)

Kerry - I tried what you suggested but I still get the entire
thing...the lastest date for the specific ID doesn't come up.

Ofer - I will try what you suggested...

thanks to all for the quick replies...

peace!