MAX Date query - how to have a 'unique' date?

[mattutd]

My database holds details of visits made to customers by support staff.
I am running a query which returns details of the last visit made to a customer as below with results displayed on a webpage.

SELECT Visits.Date1, customers.CustID AS customers_CustID, customers.Customer, Visits.CustID AS Visits_CustID, Visits.Visitee, Visits.VisitType, customers.RegionID, customers.NextVisit, customers.NextVisitBy, customers.Confirmed
FROM customers INNER JOIN Visits ON customers.CustID = Visits.CustID
WHERE (((Visits.Date1)=(SELECT MAX (Visits2.Date1) FROM Visits AS Visits2 WHERE Visits2.CustID = Visits.CustID)))
ORDER BY customers.RegionID, customers.CustID

SELECT MAX on Date1 is used to select the most recent (largest) date. The problem I have is that if 2 different staff members visit the same customer on the same day, the query logically returns 2 MAX date results for that customer, whereas I only want 1 result per customer. As the dates are the same I am not sure how to proceed. It doesn't matter which of the 2 results are returned. Does anyone have any ideas? Thanks in advance

ps - I can't perform MAX on the VisitID as the support staff don't always enter the visit data in the correct order meaning that an older visit date might have a higher VisitID than a more recent visit.

 

[Bodisathva]

You need to add the CustID constraint with the VistDate constraint:
Two choices:

WHERE ((visits.Date1=(SELECT MAX (Visits2.Date1) 
                        FROM Visits AS Visits2 
                       WHERE Visits2.CustID = Visits.CustID))
AND (Visits.CustID = customers_CustID)

or simply change the first SELECT statement to SELECT DISTINCTROW, which should eliminate the extra artifact without the additional constraints.

 

[mattutd]

Thank you for your quick reply Bodisathva. Unfortunately your suggestions don't work. I am needing to choose between equal values eg. John & Bob both visit Acme Ltd on 10/05/2007. Both enter visits into the Access database, so there are 2 'last' visits to Acme Ltd whereas I only want to display 1 last visit. (Both are valid visits and either will do, but only 1).

SELECT MAX returns both maximum dates and cannot choose between equal values - Is there any way around this?
Thanks again.

 

[Dennisk]

you may require another query to Select the First of the Max or The TOP 1 may suffice