Query

[tsison7]

I have a query which gathers a product list from an imported ship history
table. To do this I use a group by on [ProdID] and Max on [ShipDate] and
[UnitPrice].

My problem is this makes a list where the last Ship Date is obtained and the
highest price is listed. Instead I would like it to list the Unit Price
associated with the Ship Date (ie. the last shipment price)

How would I do this?

 

[Jeanette Cunningham]

Hi,
you need the Max on [ShipDate] without the Max on [UnitPrice].
You might find it easier to get the Max of [ShipDate] then use that to look
up the [UnitPrice] for all products for that date.

Jeanette Cunningham

 

[John W. Vinson]

I have a query which gathers a product list from an imported ship history
table. To do this I use a group by on [ProdID] and Max on [ShipDate] and
[UnitPrice].

My problem is this makes a list where the last Ship Date is obtained and the
highest price is listed. Instead I would like it to list the Unit Price
associated with the Ship Date (ie. the last shipment price)

How would I do this?

You'll need a Subquery instead. The Max, as you note, will potentially get
data from two different records.

Try putting an expression in a vacant Field cell:

LatestPrice: (SELECT [UnitPrice] FROM [tablename] AS X WHERE X.[ProdID] =
[tablename].[ProdID] AND X.[ShipDate] = (SELECT Max([ShipDate] FROM
[tablename] AS Y WHERE Y.[ProdID] = [tablename].[ProdID]))


John W. Vinson [MVP]