How to Select the Highest Number or Date

[MikeE1652]

I have two table in MS Access, Bids and Remarks the are linked by a BidID One-to-Many. Bids have no duplicated. Remarks has many duplicates.

I want to select a BidID in Bids tables and only the highest (newest) remark to that Bid by the newest RemDate or RemID.

Bids Table Remarks Table
BidID -------------> BidID
Bid Name RemID
Customer Name RemDate
Bid Manager Remarks

Thanks
Mike

[PhilOfWalton]

Air Code

 
SELECT BidsTable.BidID, BidsTable.BidName, BidsTable.CustomerName
Bidstable.BidManager, RemarksTable.RemID, RemarksTable.Remark, 
RemarksTable.RemDate
FROM BidsTable INNER JOIN RemarksTable 
ON BidsTable.BidID = RemarksTable.BidID
WHERE RemarksTable.RemarkDate = DMax("RemarkDate", "RemarksTable", 
"BidID = " & BidTable!BidID);
 

Incidentally, you need the comparison with the Remarks Date, noy the REMID, because, although unlikely the date for a particular REMID could be changed. There is no guarantee that they are both in ascending order.

You will need to do an ORDER BY to get things in the correct order

Phil

[jforbes]

Something like this should work:

SELECT
  BidID
, [Customer Name]
, [Bid Manager]
, (SELECT TOP 1 Remarks 
   FROM Remarks 
   WHERE Bids.BidID=Remarks.BidID
   ORDER BY RemDate DESC) AS LatestRemark
FROM Bids

[MikeE1652]

Hi Phil

Thanks, I'll try it and get back to you.

Mike