Forum Discussion

dbm11206's avatar
dbm11206
Copper Contributor
Dec 26, 2021

Query for records in foreign table has multiple results

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.

 

 

access

2 Replies

  • arnel_gp's avatar
    arnel_gp
    Iron Contributor
    Dec 27, 2021
    Select * From tblCustomer Where DCount("1","tblCustomerLocation","CustomerID = " & [CustomerID] & " And State = 'FL'") <> 0 And DCount("1","tblCustomerLocation","CustomerID = " & [CustomerID] & " And State = 'NJ'") <> 0;
    • dbm11206's avatar
      dbm11206
      Copper Contributor
      Dec 27, 2021

      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.

Resources