Show last residence using a query

[jeffyyy]

I have 2 tables, [Residents] and [Units]. In the [Units] table, there are listing of current and previous units with the start and end dates. Also, there is a yes/no checkbox that tells whether or not the unit is active (the current unit).
I want a query that will tell me the last unit the person was in. So I want it to pull the unit with no active checkbox with the most recent end date.

Can anyone tell me the best way to go about this?



Thanks for all the help!

[slave138]

Just create your query and in the criteria for the Date field, use the DMax function:

DMax("dDate","tblTable","tblTable.Resident=" & Chr(34) & [Resident] & Chr(34) & " AND tblTable.Active <> -1")

This assumes the dDate you want to find is listed in a dDate field of the table tblTable and that the resident is also listed in the same table in the Resident field. You may need to play around a little with the [Resident] section to get it to work with your actual DB.

It will return only the inactive record with the most recent date for the selected Resident.

[weekend00]

Dmax is the easiest way.

but it is very inefficient. joinning with a sub query is much faster.

[slave138]

I agree that the query would be faster (and probably easier). I tried to create a working sample but found the table descriptions to be a little confusing.

Is there any info the Resident table is providing which is not in the Unit table?