In the only query in the attached database, the results show duplicate horse names and one horse, "Nash", which should not appear in the query results. What I want to do follows:
I want to select all records where 1) the greatest (LAST or MAXimum) transfer date was in 2011 (datTransferDate in table tblHorsesArrivalsAndDepart
ures is Between #1/1/2011# And #12/31/2011#) AND THEN
the Reason for Transfer was 'Adoption' or 'Transfer Ownership Out' (lngReasonForTransfer in table tblHorsesArrivalsAndDepart
ures = 2 or 5).
Problem #1, The same horse appears twice:
Joy II, Ouija. and Warrior appear twice probably because they were adopted twice in 2011. But the horse came back after the first adoption so that adoption does not count and should not be in the report.
Problem #2, a horse that was adopted out was returned and still shows up in the report: Nash was adopted out on 2/24/11 (reason code 2) but came back on 4/1/11 (reason code 12).
Thus if the query would process the criteria sequentially, i.e. get the last transfer date and then
see if the reason code was a 2 or a 5 it would work, I think. Any ideas?