Selecting all max values of field 1 for each unique field 2

[Chris Ryner]

I have a table that contains just 4 fields and an ID - autonumber.

tblHIST

itemno actdate actqty actcost id
123 1020501 2 1.00 1
123 1030104 1 1.21 2
123 1040901 5 1.30 3
123 1050505 2 1.41 4
456 1040101 1 3.16 5
456 1041101 5 3.20 6
789 1030316 2 7.92 7
789 1040501 3 7.89 8
789 1050101 2 7.69 9

I need to extract (make a table or query that will display all of the item
numbers only once with the actcost of the record with the greatest actdate
that is less than or equal to 1041031 - so in the above example I need the
following result. The table has approx 85,000 recs and 17,000 unique
itemno's.

itemno actdate actqty actcost id
123 1040901 5 1.30 3
456 1040101 1 3.16 5
789 1040501 3 7.89 8

Can anyone point me in the right direction.

Thanks

 

[Ofer]

Create two queries
1. A group by query that returns the itemno with it max actdate, when its

SELECT MyTable.itemno, Max(MyTable.actdate) AS MaxOfactdate
FROM MyTable
GROUP BY MyTable.itemno
HAVING (((Max(MyTable.actdate))<=1041031 ))

2. Second query that link the above query with the table by the itemno and
MaxOfactdate = actdate
And display all the fields from the table

 

[giorgio rancati]

Hi, Chris

example1 with subquery
----
SELECT *
FROM tblHIST
WHERE Id=(Select Top 1 Id
From tblHIST H2
Where H2.itemno=tblHIST.itemno
And actdate<=1041031
Order By actdate Desc)
----

example2 with derived table
----
SELECT tblHIST.*
FROM tblHIST
INNER JOIN
(Select Max(actdate) AS MaxActdate,
itemno
From tblHIST
Where actdate<=1041031
Group By itemno) AS DrvTbl
ON tblHIST.itemno=DrvTbl.itemno
AND tblHIST.actdate=DrvTbl.MaxActdate

 

[Chris Ryner]

Thanks this worked exactly as I had envisioned. Thanks Ofer and everyone
that sticks around always helping others out of the goodness of their hearts!

 

[Ofer]

Thank you.

Thanks this worked exactly as I had envisioned. Thanks Ofer and everyone
that sticks around always helping others out of the goodness of their hearts!