Query for records in foreign table has multiple results

[dbm11206]

Hi, i am looking for assistance to figure out how to query the following.

I have a tblCustomer table with the primary Key CustomerID a 2nd table tblCustomerLocation where each customer can have multiple locations.

How can i query for all customers who have a location in specific 2 states i.e. FL and NJ

In("FL", "NJ") will show all customers who have a location in one of the 2 states. i am looking for those who have in both.

Assistance is greatly appreciated.

[#2]

Select * From tblCustomer Where DCount("1","tblCustomerLocation","CustomerID = " & [CustomerID] & " And State = 'FL'") <> 0 And DCount("1","tblCustomerLocation","CustomerID = " & [CustomerID] & " And State = 'NJ'") <> 0;

[#3]

@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.