• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

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
0
bobbat
Asked:
bobbat
  • 3
  • 2
3 Solutions
 
Eric FlammSenior ConsultantCommented:
I think you need to create a subquery to get the last transfer date for each horse, then use it to "select" (through a join) the ArrivalsAndDeparture records of interest. Here's the SQL of the subquery:
SELECT ArrDep.lngReasonForTransfer, ArrDep.lngHorsesID, ArrDep.datTransferDate
FROM (
SELECT ArrDep.lngHorsesID, Max(ArrDep.datTransferDate) AS MaxOfdatTransferDate, H.strHorseBarnName
FROM tblHorses AS H RIGHT JOIN tblHorsesArrivalsAndDepartures AS ArrDep ON H.lngHorsesID = ArrDep.lngHorsesID
WHERE (((Year([datTransferDate]))=2011))
GROUP BY ArrDep.lngHorsesID, H.strHorseBarnName
ORDER BY ArrDep.lngHorsesID) qryLast
 INNER JOIN tblHorsesArrivalsAndDepartures ArrDep ON (qryLast.MaxOfdatTransferDate = ArrDep.datTransferDate) AND (qryLast.lngHorsesID = ArrDep.lngHorsesID)
WHERE (((ArrDep.lngReasonForTransfer)=2 Or (ArrDep.lngReasonForTransfer)=5));

Open in new window

This query actually has a subquery (qryLast) as well - you could add this to your database, or just copy this into the SQL code dialog for 1 query (you won't be able to use the design surface if you do that, since the design surface can't handle subqueries). If you want, just take the query in parentheses out and make it a separate query called qryLast. It finds the last date each horse was transferred, regardless of reason. Then, the surrounding query reduces this result (48 transfers) to just those with 2 or 5 as the reason (23 unique) - since the last transfer for Nash wasn't 2 or 5, he doesn't show up in this list.

Join this query on datTransferDate and HorseID back to tblHorsesArrivalsAndDepartures in your main query - that way, you'll only get the last Arrival record of each horse.
0
 
bobbatAuthor Commented:
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!
0
 
cmgarnettCommented:
How about

SELECT DISTINCT tblHorses.strHorseBarnName, Format((Date()-([datDateOfBirth]))/365.25,0) AS Age, tblHorses.strHorseColor, tblHorses.strHorseGender, tblHorses.strHorseBreed, Max(tblHorsesArrivalsAndDepartures.datTransferDate) AS MaxOfdatTransferDate, tblHorsesArrivalsAndDepartures.lngReasonForTransfer, tlkpReasonForTransfer.strReasonForTransfer, tblHorsesArrivalsAndDepartures.strCityAcquired_From, tblHorsesArrivalsAndDepartures.strStateAcquired_From, tblHorsesArrivalsAndDepartures.lngPersonsID_To
FROM tblHorses
INNER JOIN (tblHorsesArrivalsAndDepartures
INNER JOIN tlkpReasonForTransfer
ON tblHorsesArrivalsAndDepartures.lngReasonForTransfer = tlkpReasonForTransfer.lngReasonForTransferID)
ON tblHorses.lngHorsesID = tblHorsesArrivalsAndDepartures.lngHorsesID
WHERE tblHorsesArrivalsAndDepartures.datTransferDate Between #1/1/2011# And #12/31/2011#
AND tblHorsesArrivalsAndDepartures.lngReasonForTransfer IN(2,5)
AND tblHorsesArrivalsAndDepartures.datTransferDate = (SELECT MAX(datTransferDate) FROM tblHorsesArrivalsAndDepartures WHERE lngHorsesID = tblHorses.lngHorsesID)
GROUP BY tblHorses.strHorseBarnName, Format((Date()-([datDateOfBirth]))/365.25,0), tblHorses.strHorseColor, tblHorses.strHorseGender, tblHorses.strHorseBreed, tblHorsesArrivalsAndDepartures.lngReasonForTransfer, tlkpReasonForTransfer.strReasonForTransfer, tblHorsesArrivalsAndDepartures.strCityAcquired_From, tblHorsesArrivalsAndDepartures.strStateAcquired_From, tblHorsesArrivalsAndDepartures.lngPersonsID_To

I have moved the HAVING clauses into WHERE clauses and then used a subquery to extract the max transfer date for each horse and use that to compare with the transfer date on the record being processed.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Eric FlammSenior ConsultantCommented:
I'm not sure what the Group By clause does for you - and you don't really need the Max function in the predicate (start of line 2), which is what forces you to have the Group By. You don't need the Max function there because you have it in the subquery:
SELECT MAX(datTransferDate) FROM tblHorsesArrivalsAndDepartures WHERE lngHorsesID = tblHorses.lngHorsesID

Open in new window

- once you have that filter implemented through the join, you should only get 1 record (type 2 or 5) on any given day for any given horse, so there's nothing to find the Max of, if that makes sense. I'm also not sure you need the DISTINCT keyword in the predicate - again, there's only 1 row per horse anyway, so they are all DISTINCT.
SELECT  tblHorses.strHorseBarnName, Format((Date()-([datDateOfBirth]))/365.25,0) AS Age, tblHorses.strHorseColor, tblHorses.strHorseGender, tblHorses.strHorseBreed, tblHorsesArrivalsAndDepartures.datTransferDate, tblHorsesArrivalsAndDepartures.lngReasonForTransfer, tlkpReasonForTransfer.strReasonForTransfer, tblHorsesArrivalsAndDepartures.strCityAcquired_From, tblHorsesArrivalsAndDepartures.strStateAcquired_From, tblHorsesArrivalsAndDepartures.lngPersonsID_To
FROM tblHorses 
INNER JOIN (tblHorsesArrivalsAndDepartures 
INNER JOIN tlkpReasonForTransfer 
ON tblHorsesArrivalsAndDepartures.lngReasonForTransfer = tlkpReasonForTransfer.lngReasonForTransferID) 
ON tblHorses.lngHorsesID = tblHorsesArrivalsAndDepartures.lngHorsesID
WHERE tblHorsesArrivalsAndDepartures.datTransferDate Between #1/1/2011# And #12/31/2011# 
AND tblHorsesArrivalsAndDepartures.lngReasonForTransfer IN(2,5) 
AND tblHorsesArrivalsAndDepartures.datTransferDate = (SELECT MAX(datTransferDate) FROM tblHorsesArrivalsAndDepartures WHERE lngHorsesID = tblHorses.lngHorsesID)

Open in new window


Looks like it should work - does it?
0
 
bobbatAuthor Commented:
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
0
 
bobbatAuthor Commented:
Thanks guys for all your help!  You can see more info on the horses at http://www.beautysequinerescue.org/
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now