I need to select the last record from the child table in a one to many relationship.
I have found some solutions in Experts Exchange but have not been able to translate them to my database.
My simple test database has two tables
tblIncident has two fields, IncidentID (PK, AutoNumber) and LastName (Text)
tblStatus has three fields, StatusID (PK, AutoNumber), IncidentID (Foreign Key, Number), Comments (Memo)
tblStatus may have many records related to one IncidentID in tblIncident.
I want to SELECT only the last record from tblStatus per IncidentID based on the highest number StatusID
Of course I can add a date field to tblStatus if that is necessary