Robert Batchelor
asked on
How can I get this query to work correctly?
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?
AdoptionDatabase.accdb
I want to select all records where 1) the greatest (LAST or MAXimum) transfer date was in 2011 (datTransferDate in table tblHorsesArrivalsAndDepart
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?
AdoptionDatabase.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys, both queries give the same results--I just have a few more corrections to make to the data . . .
eflamm = "00qryForAdoptionReport201 1FromEE
cmgarnett: = query2
AdoptionDatabase-2.accdb
eflamm = "00qryForAdoptionReport201
cmgarnett: = query2
AdoptionDatabase-2.accdb
ASKER
Thanks guys for all your help! You can see more info on the horses at http://www.beautysequinerescue.org/
ASKER