"Last Of.." Query

Hi All,

I've been trying to figure this our for awhile and can't seem to catch where I'm going wrong.
I have these tables...

tblPurchaseOrder
PurchaseOrderID (PK)
PurchaseOrderDate

tblPurchaseOrderDetails
PurchaseOrderDetailID (PK)
PurchaseOrderID (FK)
InventoryID
PurchaseOrderQty
PriceEach

Now..I am trying to create a query that returns the Last Date a product was ordered along with the price it was ordered at.

I've tried a totals query with tblPurchaseOrder linked to tblPurchaseOrderDetails on the PurchaseOrderID field. I have a Group By on the InventoryID and a Last on PurchaseOrderDate. Then I went and created a new query and added the qryLastOrderDate, and tblPurchaseOrderDetails, linked on InventoryID and dropped down LastOfPurchaseOrderDate,InventoryID, and Price each. It returns more records than I'm wanting it to.
Any help appreciated

Try this. It works by sorting by date (descending) so that the PO date is at the "top". It then uses the TOP sql statement to list only the top record:


Note that in this example, the InventoryID is 23.

hth
Chris

Your on the right track, but off just a little. Let's call that first query you created 'LastPurchaseDate' (Group By InventoryID, Last on PurchaseOrderDate). Instead of using Last on PurchaseOrderDate, use MAX. Last just returns the last record entered, while MAX will return the highest value of the column.

After doing that do this:

1. Make a copy of 'LastPurchaseDate' and name it 'LastPurchase'.
2. Go into design view of 'LastPurchase' and bring in the LastPurchaseDate query.
3. Link the InventoryID of LastPurchaseDate to InventoryID in tblPurchaseOrderDetails.
4. Link MaxOfPurchaseOrderDate of LastPurchaseDate to PurchaseOrderDate in tblPurchaseOrder.
5. Bring the 'PriceEach' field from tblPurchaseOrderDetails down into the field list of the query so it will show.
6. Under the 'PriceEach' filed you added change it from Group By to Last.

Run the query and that should generate the results you want.

Thanks to both of you!

First and Last should be completely avoided as they are unreliable. Always use Min and Max

I have seen queries with my own eyes where First of a field previously subjected to Order By Ascending returns a different record from Last of the same field Order By Descending.

Galaxiom said:

I have seen queries with my own eyes where First of a field previously subjected to Order By Ascending returns a different record from Last of the same field Order By Descending.

Click to expand...

This isn't a valid test for First/Last. The only time that First and Last could produce the same value in your test is if there was only one record. If there is any more than one record then I would expect the values returned to be from different records.

Note that Order By will have no effect whatsoever on the results of First or Last.

I agree that First/Last are not appropriate for this solution though.

Chris