MS ACCESS - Finding field for most recent record

Question

Apologies for title, I am not sure how to phrase it.

I currently have two tables "ASSETS" and "LOANS" ASSETS contains a straightforward list of equipment It has a field "AssetID" which has a One-To-Many relationship with a field in LOANS called "LoanAssetID"

Each piece of equipment can have many loans. It can be loaned to one person, then returned, then loaned to another. There is a field in "LOANS" named "ReturnDate" in which people record the date that a piece of equipment was returned. There is also a field called "StartDate".

A piece of equipment can said to be currently 'on loan' if the most recent record (that with the most recent start date) has no return date.

However, I am not clear enough in my query writing to complete this. I need to find the most recent record as for any asset, there may be multiple return dates, but these may not always correspond to the most recent loan.

Ideally, I would then like this to calculate a field to mark the equipment as 'on loan' 'available' etc.

Thanks for any help in advance

So far I have tried a Max Query, but, probably due to me misunderstanding queries, I am confused as to why it returns more than one result.
So, currently I have tried

SELECT Max(tbl_Loans.[Start Date]) AS [MaxOfStart Date],tbl_Loans [Return Date], tbl_Loans.LoanAssetID
FROM tbl_Loans
GROUP BY tbl_Loans.[Return Date], tbl_Loans.LoanAssetID
HAVING (((tbl_Loans.[Return Date]) Is Null));

However, what I would like is the most recent value only for any given LoanAssetID; what I get is more than one field for each given LoanAssetID where more than one result exists.

Thank you for your help

Have you tried something yet? This sounds like a homework problem. — Tim Biegeleisen
can you create a query that returns the max(startDate) for each assetID in the loans table? — Beth
I'm glad it sounds simple, because I am struggling a touch. I appreciate this is probably easy for others and do appreciate you taking the time to help. — TerrorPenguin

MoondogsMaDawg MoondogsMaDawg · Accepted Answer · 2017-01-04T15:46:02

The query is grouping by return date, which I don't believe you want. Try:

SELECT Max(tbl_Loans.[Start Date]) AS [MaxOfStart Date],tbl_Loans.[Return Date], tbl_Loans.LoanAssetID
FROM tbl_Loans
GROUP BY tbl_Loans.LoanAssetID
HAVING tbl_Loans.[Return Date] Is Null;

Alternatively, if your system is robust enough to assume that any null return date will correspond with the most recent loan since an asset that has yet to be returned could not be lent to a different individual, you could just search the table for Null return dates:

SELECT tbl_Loans.LoanAssetID
FROM tbl_Loans
GROUP BY tbl_Loans.LoanAssetID
HAVING tbl_Loans.[Return Date] Is Null

MS ACCESS - Finding field for most recent record

1 Answers