CODEWITHSUNDEEP
ms-access
Scott Jethro Hardwick
Scott Jethro Hardwick

Reputation: 65

Getting the last date from a set of records

********EDITED*********

Basically what I am ultimately looking for is the last appointment a dog was in our shop with details of that appointment. As you know, right now I am just pulling the MAX date, which we have customers that are actually booked out for the entire year, so I am not getting their LAST appointment IN, but their LAST appointment BOOKED in the future.

SELECT dbo_Pet.petId, 
         dbo_Pet.petName, 
         dbo_Customer.cstLName, 
         dbo_vwApptGrid.aptDate, 
         dbo_vwApptGrid.aptId, 
         dbo_vwApptGrid.GroomStyle, 
         dbo_vwApptGrid.StatusDescr
FROM (dbo_Customer RIGHT JOIN dbo_Pet ON dbo_Customer.[cstId] = dbo_Pet.[petCustId]) 
         LEFT JOIN dbo_vwApptGrid ON dbo_Pet.petId = dbo_vwApptGrid.aptPetId
WHERE (((dbo_Pet.petId)=[ID]) 
         AND ((dbo_vwApptGrid.aptDate)<=Date()) 
         AND ((dbo_vwApptGrid.aptDate)=(SELECT Max(tMax.aptDate) 
FROM dbo_vwApptGrid As tMax 
WHERE tMax.aptPetId = dbo_Pet.petId)));

shows no records at all. If I change max date to greater than today it shows the last booked appointment, like it should. Why is this not limiting my max date to less than today??

Upvotes: 0

Views: 43

Answers (1)

Erik A
Erik A

Reputation: 32632

There are multiple ways to go about this, but I prefer filtering by a totals subquery (feels the most descriptive for what I want to me).

The solution commented by @June7 is also valid (especially if you don't need the fields he/she referred to).

When adding additional criteria, always add these to the subquery, never to the main query. If you add them to the main query, you won't get any results, since the subquery will return something that is filtered out.

SELECT dbo_Pet.petName
    ,dbo_Customer.cstLName
    ,dbo_vwApptGrid.aptId
    ,dbo_vwApptGrid.aptDate AS most_recent
    ,dbo_vwApptGrid.GroomStyle
    ,dbo_vwApptGrid.StatusDescr
FROM (
    dbo_Customer RIGHT JOIN dbo_Pet ON dbo_Customer.[cstId] = dbo_Pet.[petCustId]
    )
LEFT JOIN dbo_vwApptGrid ON dbo_Pet.petId = dbo_vwApptGrid.aptPetId

WHERE dbo_Pet.petId = [ID] 
AND dbo_vwApptGrid.aptDate = (SELECT Max(tMax.aptDate) FROM dbo_vwApptGrid As tMax WHERE tMax.aptPetId = dbo_Pet.petId AND tMax.aptDate <= Date());

Upvotes: 1

Related Questions