Access Database Need to fetch a record with most recent date from another table

Question

I have been stuck on this query for hours and Hope an Access Guru can help me out.

I am writing a SELECT query to inner join with another table (1 to many). I need to only fetch the record with the most recent Date. I research and tried many query examples which did not work.

The closest I have been is this one

SELECT 
tblData.RecentDate,
tblData.ID,
tblData.Name,
tblData.Address

FROM [table1] 

INNER JOIN 
        (   SELECT Top 1 *
            FROM    [table2] bh1
            ORDER BY bh1.RecentDate DESC
        ) tblData
ON [table1].[ID] = tblData.[ID])

The Top 1 does not give me a record for each ID. If I remove the Top 1, I see all the records with the ID but I noticed that the RecentDate field is blank. If I leave the Top 1 in the query, records for ID 1 and 4 is not in the result.

I also tried the following

INNER JOIN 
        (   SELECT   Max(bh1.RecentDate) as RecentDate, bh1.Name, branchhistory.Address, bh1.City,
            FROM    [table2] bh1
            GROUP BY bh1.ID
        ) tblData
ON [table1].[ID] = tblData.[UDID]

This query only works if I select Max(bh1.RecentDate) as RecentDate, bh1.ID and groupby the bh1.ID... But I have other fields to fetch which will require to add to the Group By and will not return just 1 record.

From my inner join, I need to fetch the record with many fields and fetch by the most recent date value.

I just tried the following but not all the IDs are coming back

INNER JOIN 
        (   SELECT  *
            FROM    [table2] bh1
            WHERE bh1.RecentDate= (SELECT Max(bh2.RecentDate) from  [table2] as bh2 WHERE bh1.ID =  bh2.ID )

        ) tblData
ON [table1].[ID] = tblData.[ID]

Any help is greatly appreciated.

Thanks

Answer 1

Save this as a permanent query:

SELECT ID, Max(RecentDate) AS MaxDate FROM table2 GROUP BY ID

Then join table 1 to that query by ID and then to table2 by ID and MaxDate=Recentdate