SQL - Extract latest transaction dates from a table grouped by asset (MS-ACCESS 2010)

Question

I am building a query to display the most current transactions (date) from a transaction table which are grouped by "Asset" (column B) so I can assign a current status to the asset.

Table example TranX ID | Asset | Date Out | Date In | 001 | 1001 | 20-01-2023 | 31-02-2023 | 002 | 1001 | 03-03-2023 | 12-04-2023 | 003 | 1002 | 06-03-2023 | 13-04-2023 | 004 | 1002 | 18-04-2023 | 12-12-2023 | 005 | 1002 | 01-02-2024 | | etc

Query would display 002 | 1001 | 03-03-2023 | 12-04-2023 | Available 005 | 1002 | 01-02-2024 | | In Use

I have a query created to display the transactions

SELECT Transactions.ID, Transactions.Asset, Transactions.[Checked Out To], Transactions.[Checked Out Date], Transactions.[Checked in Date], [Contacts Extended].[Contact Name], Transactions.Notes FROM Transactions INNER JOIN [Contacts Extended] ON Transactions.[Checked Out To] = [Contacts Extended].ID;

I have tried to use the following SQL code to attempt what I am looking for

SELECT Transactions.ID, Transactions.Asset, Transactions.[Checked Out To], Transactions.[Checked Out Date], Transactions.[Checked in Date], [Contacts Extended].[Contact Name] FROM Transactions inner join (select asset, max([Checked Out Date]) as MaxDate from Transactions group by asset) tm on Transactions.[Asset] = tm.Asset and t.[Checked Out Date] = tm.MaxDate

But I am not very knowledgeable on SQL Expression building / editing and this code only works on one date column

Any help appreciated and I will apply learning to all answers.

Answer 1

First you make an assumption that the date ranges have been recorded reliably and don't overlap. If not, you'll have to think about cleaning that up first.

Then you create a subquery in the Where clause to bring back only the latest Date Out record and code an Iif() function to present status:

Select *, Iif("Date In" is null,'In Use','Available') as Status
from TranX O
Where "Date Out"=(
    Select max("Date Out") From TranX I
    Where O.Asset=I.Asset
)