Never mind I figured it out :)

>How would it look like if I wanted to do it on SQL Server? I know I posted VFP syntax - but want to try it in SQL Server now.....
>
>
>>You can use derived tables, e.g.
>>
>>

>>
>>SELECT cust.Customer_id, cust.Customer_name, v.visit_date,;
>>v.location_id, v.Visits_id;
>>FROM ;
>>hpmg!Customer cust ;
>>INNER JOIN hpmg!Visits v ;
>>ON cust.Customer_id = v.Customer_id;
>>inner join (select Customer_id, visit_date, location_id ;
>>from  hpmg!Visits ;
>>WHERE visit_date between  ?StartOfTheYear and ?EndOfTheYear ;
>>GROUP BY Customer_id, visit_date, location_id ;
>>HAVING COUNT(*) > 1 ) as MultipleVisits ON v.Customer_id = MultipleVisits.Customer_ID ;
>>AND v.visit_date = MultipleVisits.visit_date and v.Location_id = MultipleVisits.Location_id;
>>WHERE v.visit_date between  ?StartOfTheYear and ?EndOfTheYear 
>>
>>

>>
>>I also suggest to use date column as is if you have index on date column and not on year(date) (In the latter case use your original where).
>>
>>>I think I'm doing something dumb here - 3 select statements but seems like there should be a way to do this in just one????
>>>
>>>First table: Customer
>>>customer_id
>>>customer_name
>>>
>>>Second table: Visits
>>>visits_id
>>>customer_id
>>>visit_date
>>>location_id
>>>
>>>...what I want is customer_id, customer_name, visit_id, visit_date and location_id for all location_id's where a customer visited more than once on the same day for a particular year.
>>>This is what I'm doing now - but like I said, seems like there should be a better way.
>>>----------------------------------------------------------
>>>
>>>

>>>SELECT Customer.Customer_id, Customer.Customer_name, Visits.visit_date,;
>>>  Visits.location_id, Visits.Visits_id, 0 AS vcount;
>>> FROM ;
>>>     hpmg!Customer ;
>>>    LEFT OUTER JOIN hpmg!Visits ;
>>>   ON  Customer.Customer_id = Visits.Customer_id;
>>> WHERE  YEAR(Visits.visit_date) = ( ?lnYear) INTO CURSOR curHold_1
>>>

>>>
>>>

>>> SELECT Customer.Customer_id, Visits.visit_date, Visits.location_id,;
>>>  COUNT(*) AS vcount;
>>> FROM ;
>>>     hpmg!Customer ;
>>>    LEFT OUTER JOIN hpmg!Visits ;
>>>   ON  Customer.Customer_id = Visits.Customer_id;
>>> WHERE  YEAR(Visits.visit_date) = ( 2015 );
>>> GROUP BY Customer.Customer_id, Visits.visit_date, Visits.location_id;
>>> HAVING  ( vcount ) > ( 1 ) INTO CURSOR curHold_2
>>>

>>>
>>>

>>> SELECT curHold_1.Customer_id, curHold_1.Customer_name, curHold_1.Visits_id,;
>>>  curHold_1.visit_date, curHold_1.location_id;
>>> FROM ;
>>>     curHold_1 ;
>>>    INNER JOIN curHold_2 ;
>>>   ON  curHold_1.Customer_id = curHold_2.Customer_id;
>>> WHERE (  curHold_1.Customer_id = curHold_2.Customer_id;
>>>   AND  curHold_1.visit_date = curHold_2.visit_date );
>>>   AND  curHold_1.location_id = curHold_2.location_id
>>>

>>>
>>>...I suppose I could leave it this way but I'd really like a cleaner way to do it.