Link to home
Start Free TrialLog in
Avatar of Robert Batchelor
Robert BatchelorFlag for United States of America

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 tblHorsesArrivalsAndDepartures is Between #1/1/2011# And #12/31/2011#)  AND THEN the Reason for Transfer was 'Adoption' or 'Transfer Ownership Out' (lngReasonForTransfer in table tblHorsesArrivalsAndDepartures = 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
ASKER CERTIFIED SOLUTION
Avatar of Eric Flamm
Eric Flamm
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Robert Batchelor

ASKER

Thank you, we are getting closer.  The results were not as I expected because of the errors in my data.  I will correct the data and check the results again soon, but so far, so good!
SOLUTION
Avatar of cmgarnett
cmgarnett
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks guys, both queries give the same results--I just have a few more corrections to make to the data . . .
eflamm = "00qryForAdoptionReport2011FromEE
cmgarnett: = query2
AdoptionDatabase-2.accdb
Thanks guys for all your help!  You can see more info on the horses at http://www.beautysequinerescue.org/