Group by problem

[Guest]

Hi,
I have a table with fields as follow:
PartNo, PoDate, Price, Contract
for each part there is a separate record representing each purchase order
there is no primary key.

I want to create a select query to get
find the LATEST PoDate entry for each part
and then give me the corresponding Contract and price

Using GroupBy PartNo and Max in the PoDate i can get the
latest PartNumber but i dont know how to go about getting the
CORRESPONDING contract and Price - i cant find anything meaningfull
to put int their respective totals section in the query builder.

could someone please advise me on the best way to solve this problem
Thanks
Shaun

 

[John Vinson]

Hi,
I have a table with fields as follow:
PartNo, PoDate, Price, Contract
for each part there is a separate record representing each purchase order
there is no primary key.

So you could have three, or eighteen, records all with the same
partno, podate, price, and contract? No control over duplicate data?
Unwise! Note that a Primary Key can contain up to ten fields; might it
not make sense to make PartNo, PoDate, and Contract a joint
three-field PK so that you can't double enter orders for the same part
to the same contract on the same day?

I want to create a select query to get
find the LATEST PoDate entry for each part
and then give me the corresponding Contract and price

Using GroupBy PartNo and Max in the PoDate i can get the
latest PartNumber but i dont know how to go about getting the
CORRESPONDING contract and Price - i cant find anything meaningfull
to put int their respective totals section in the query builder.

Use a Subquery. Create a Query based on the table; on the criteria
line under PoDate put

=(SELECT Max([PoDate]) FROM yourtable AS X WHERE X.PartNo =
yourtable.[PartNo])

John W. Vinson[MVP]