arnel_gp Thank you very much for the suggestion.
I got 3 ideas and tested all of them and here are my results in performance.
But first a little background. There is 58k records in the tblcustomer. The customer location table has 66.5k records so there is a performance hit when running heavy queries.
I need this as part of a function to build the criteria for a continues form.
The result I was looking for is 688 customers.
Option 1 using a sum subquery as the FROM took 122.594 seconds
strLocationFilter = "((tblCustomerLocation.CustomerID) In ((SELECT sQryCustomerLocationJoin.CustomerID FROM (SELECT DISTINCT tblCustomerLocation.CustomerID, tblCustomerLocation.LocationID FROM tblCustomerLocation GROUP BY tblCustomerLocation.CustomerID, tblCustomerLocation.LocationID HAVING (((tblCustomerLocation.LocationID) In (" & strLocation & ")))) AS sQryCustomerLocationJoin GROUP BY sQryCustomerLocationJoin.CustomerID HAVING (((Count(sQryCustomerLocationJoin.CustomerID))=2));)))"
Option 2 using the suggested Dcount took 206.641 seconds
strLocationFilter = "((tblCustomerLocation.CustomerID) In ((SELECT tblCustomerLocation.CustomerID FROM tblCustomerLocation WHERE (((DCount('CustomerLocationID', 'tblCustomerLocation', '[CustomerID] = ' & [CustomerID] & ' AND [LocationID] = 52')) > 0) And ((DCount('CustomerLocationID', 'tblCustomerLocation', '[CustomerID] = ' & [CustomerID] & ' AND [LocationID] = 148')) > 0)) GROUP BY tblCustomerLocation.CustomerID;)))"
Option 3 using Multiple exists subqueries took 0.312 seconds!!!!!
Created a loop to get all the subqueries
Dim lngRow As Long
With Me.ListLocationsSelected
For lngRow = 0 To .ListCount - 1
If Len(Format(strLocationFilterNEW)) > 0 Then
strLocationFilter = strLocationFilter & " AND "
End If
strLocationFilter = strLocationFilter & "Exists (SELECT tblCustomerLocation.CustomerID FROM tblCustomerLocation WHERE tblCustomerLocation.CustomerID = tblCustomer.CustomerID And tblCustomerLocation.LocationID = " & .ItemData(lngRow) & ")"
Next lngRow
End With
End If
If anyone has any suggestion or comment, please let me know.
Thank you in advance.