Need help with SQL statement

[Euler271]

I have two tables: One with a company ID, counties and states where they do business. The second table has customer data using companies from the first table with customer state, county, visits, amount spent.

I need to calculate customer information from outside the counties where the company does business in a particular state and outside the states where the company does business. Here's an example:

Company X has locations in Platte and Jackson counties in Missouri and Johnson county in Kansas. Since it does business in two states I need three calculations. If the company does business in Y states I would need Y + 1 calculations:
1) customers, visits and amounts spent from counties other than Platte and Jackson but in Missouri;
2) customers, visits and amounts spent from counties other than Johnson in Kansas;
3) customers, visits and amounts from states other than Missouri and Kansas.

If a company only does business in one state then I need to add two calculations:


1) customers, visits and amounts spent from counties other those counties in which the company is located in the single state PLUS
2) customers, visits and amounts spent from anywhere outside the state in which the company is located

I tried doing this using VBA loops but with over 17K company IDs, but it takes days to complete, even on the C: drive (not the network), which is untenable.

I'm looking for a clever SQL statement (or series of SQL statements) that will get the job done much quicker.

Is there such a thing or are the VBA loops the only way to go? Thanks for any help.

[orange]

What have you tried?
Show us the code.

After reading this a few times

I need to calculate customer information from outside the counties where the company does business in a particular state and outside the states where the company does business.

I think a few examples would help clarify your set up and requirement.
How do visits and amounts fit into this?

[Euler271]

Here is the code I've tried:

Sub ZZs()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim rs3 As DAO.Recordset
Dim rs4 As DAO.Recordset
Dim strCounty As String
Dim strState As String
Dim sqlString As String
Call MakeNewTable 'Destroy then recreate ZZ table
Set db = CurrentDb
Set rs1 = db.OpenRecordset("SELECT T2.ID FROM T2 GROUP BY T2.ID ORDER BY T2.ID;")
Set rs4 = db.OpenRecordset("ZZ")
rs1.MoveLast
rs1.MoveFirst
Do Until rs1.EOF 'Loop through IDs in T2
'Get list of company STATES for the particular ID
Set rs2 = db.OpenRecordset("SELECT T2.State FROM T2 WHERE (((T2.ID) = '" & rs1!ID & "')) GROUP BY T2.State;")
rs2.MoveLast
rs2.MoveFirst
Select Case rs2.RecordCount
Case 1 'company in 1 STATE only
'Get list of company COUNTIES for the particular ID and STATE
Set rs3 = db.OpenRecordset("SELECT T2.County FROM T2 WHERE (((T2.State) = '" & rs2!State & "') And ((T2.ID) = '" & rs1!ID & "')) GROUP BY T2.County;")
'Add record showing values for same STATE but outside of company COUNTIES + values for outside of company STATES
strCounty = "("
Do Until rs3.EOF
strCounty = strCounty & rs3!County & ", "
rs3.MoveNext
Loop
strCounty = Left(strCounty, Len(strCounty) - 2) & ")"
rs4.AddNew
rs4!ID = rs1!ID
rs4!County = "ZZ-County"
rs4!Customers = DSum("CustomerCount", "Report", "ID='" & rs1!ID & "' AND CustomerState='" & rs2!State & "' AND CustomerCounty NOT IN " & strCounty) + DSum("CustomerCount", "Report", "ID='" & rs1!ID & "' AND CustomerState NOT LIKE '" & rs2!State & "'")
rs4!Visits = DSum("VisitCount", "Report", "ID='" & rs1!ID & "' AND CustomerState='" & rs2!State & "' AND CustomerCounty NOT IN " & strCounty) + DSum("VisitCount", "Report", "ID='" & rs1!ID & "' AND CustomerState NOT LIKE '" & rs2!State & "'")
rs4!BillAmt = DSum("BillAmount", "Report", "ID='" & rs1!ID & "' AND CustomerState='" & rs2!State & "' AND CustomerCounty NOT IN " & strCounty) + DSum("BillAmount", "Report", "ID='" & rs1!ID & "' AND CustomerState NOT LIKE '" & rs2!State & "'")
rs4.Update
Case Else 'company in >1 STATE
strState = "("
Do Until rs2.EOF 'Create list of STATES by ID
strState = strState & rs2!State & ", "
rs2.MoveNext
Loop
strState = Left(strState, Len(strState) - 2) & ")"
'Move back to the start of the recordset
rs2.MoveFirst
Do Until rs2.EOF 'Loop through STATES by ID
'Get list of company COUNTIES for the particular ID and STATE
Set rs3 = db.OpenRecordset("SELECT T2.County FROM T2 WHERE (((T2.State) = '" & rs2!State & "') And ((T2.ID) = '" & rs1!ID & "')) GROUP BY T2.County;")
strCounty = "("
Do Until rs3.EOF 'Create list of COUNTIES by STATE by ID
strCounty = strCounty & rs3!County & ", "
rs3.MoveNext
Loop
strCounty = Left(strCounty, Len(strCounty) - 2) & ")"
'Add record showing values for same STATE but outside of company COUNTIES
rs4.AddNew
rs4!ID = rs1!ID
rs4!County = "ZZ-County"
rs4!Customers = DSum("CustomerCount", "Report", "ID='" & rs1!ID & "' AND CustomerState='" & rs2!State & "' AND CustomerCounty NOT IN " & strCounty)
rs4!Visits = DSum("VisitCount", "Report", "ID='" & rs1!ID & "' AND CustomerState='" & rs2!State & "' AND CustomerCounty NOT IN " & strCounty)
rs4!BillAmt = DSum("BillAmount", "Report", "ID='" & rs1!ID & "' AND CustomerState='" & rs2!State & "' AND CustomerCounty NOT IN " & strCounty)
rs4.Update
rs2.MoveNext
Loop
'The following is for ZZ-State and only needs to run once per ID so it's outside the state loop
rs4.AddNew
rs4!ID = rs1!ID
rs4!County = "ZZ-State"
rs4!Customers = DSum("CustomerCount", "Report", "ID='" & rs1!ID & "' AND CustomerState NOT IN " & strState)
rs4!Visits = DSum("VisitCount", "Report", "ID='" & rs1!ID & "' AND CustomerState NOT IN " & strState)
rs4!BillAmt = DSum("BillAmount", "Report", "ID='" & rs1!ID & "' AND CustomerState NOT IN " & strState)
rs4.Update
End Select
rs1.MoveNext
Loop
rs1.Close
rs2.Close
rs3.Close
rs4.Close
End Sub

T2 is the table with company information.
ZZ is the table where the results are appended.
Report is the table containing customer data.


'''code tags added and code reformatted

'reformatted by orange
Sub ZZs()
    Dim db As DAO.Database
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim rs3 As DAO.Recordset
    Dim rs4 As DAO.Recordset
    Dim strCounty As String
    Dim strState As String
    Dim sqlString As String
    Call MakeNewTable    'Destroy then recreate ZZ table
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("SELECT T2.ID FROM T2 GROUP BY T2.ID ORDER BY T2.ID;")
    Set rs4 = db.OpenRecordset("ZZ")
    rs1.MoveLast
    rs1.MoveFirst
    Do Until rs1.EOF    'Loop through IDs in T2
        'Get list of company STATES for the particular ID
        Set rs2 = db.OpenRecordset("SELECT T2.State FROM T2 WHERE (((T2.ID) = '" & rs1!id & "')) GROUP BY T2.State;")
        rs2.MoveLast
        rs2.MoveFirst
        Select Case rs2.RecordCount
        Case 1    'company in 1 STATE only
            'Get list of company COUNTIES for the particular ID and STATE
            Set rs3 = db.OpenRecordset("SELECT T2.County FROM T2 WHERE (((T2.State) = '" & rs2!State & "') And ((T2.ID) = '" & rs1!id & "')) GROUP BY T2.County;")
            'Add record showing values for same STATE but outside of company COUNTIES + values for outside of company STATES
            strCounty = "("
            Do Until rs3.EOF
                strCounty = strCounty & rs3!County & ", "
                rs3.MoveNext
            Loop
            strCounty = Left(strCounty, Len(strCounty) - 2) & ")"
            rs4.AddNew
            rs4!id = rs1!id
            rs4!County = "ZZ-County"
            rs4!Members = DSum("CustomerCount", "Report", "ID='" & rs1!id & "' AND CustomerState='" & rs2!State & "' AND CustomerCounty NOT IN " & strCounty) + DSum("CustomerCount", "Report", "ID='" & rs1!id & "' AND CustomerState NOT LIKE '" & rs2!State & "'")
            rs4!Visits = DSum("VisitCount", "Report", "ID='" & rs1!id & "' AND CustomerState='" & rs2!State & "' AND CustomerCounty NOT IN " & strCounty) + DSum("VisitCount", "Report", "ID='" & rs1!id & "' AND CustomerState NOT LIKE '" & rs2!State & "'")
            rs4!BillAmt = DSum("BillAmount", "Report", "ID='" & rs1!id & "' AND CustomerState='" & rs2!State & "' AND CustomerCounty NOT IN " & strCounty) + DSum("BillAmount", "Report", "ID='" & rs1!id & "' AND CustomerState NOT LIKE '" & rs2!State & "'")
            rs4.Update
        Case Else    'company in >1 STATE
            strState = "("
            Do Until rs2.EOF    'Create list of STATES by ID
                strState = strState & rs2!State & ", "
                rs2.MoveNext
            Loop
            strState = Left(strState, Len(strState) - 2) & ")"
            'Move back to the start of the recordset
            rs2.MoveFirst
            Do Until rs2.EOF    'Loop through STATES by ID
                'Get list of company COUNTIES for the particular ID and STATE
                Set rs3 = db.OpenRecordset("SELECT T2.County FROM T2 WHERE (((T2.State) = '" & rs2!State & "') And ((T2.ID) = '" & rs1!id & "')) GROUP BY T2.County;")
                strCounty = "("
                Do Until rs3.EOF    'Create list of COUNTIES by STATE by ID
                    strCounty = strCounty & rs3!County & ", "
                    rs3.MoveNext
                Loop
                strCounty = Left(strCounty, Len(strCounty) - 2) & ")"
                'Add record showing values for same STATE but outside of company COUNTIES
                rs4.AddNew
                rs4!id = rs1!id
                rs4!County = "ZZ-County"
                rs4!Members = DSum("CustomerCount", "Report", "ID='" & rs1!id & "' AND CustomerState='" & rs2!State & "' AND CustomerCounty NOT IN " & strCounty)
                rs4!Visits = DSum("VisitCount", "Report", "ID='" & rs1!id & "' AND CustomerState='" & rs2!State & "' AND CustomerCounty NOT IN " & strCounty)
                rs4!BillAmt = DSum("BillAmount", "Report", "ID='" & rs1!id & "' AND CustomerState='" & rs2!State & "' AND CustomerCounty NOT IN " & strCounty)
                rs4.Update
                rs2.MoveNext
            Loop
            'The following is for ZZ-State and only needs to run once per ID so it's outside the state loop
            rs4.AddNew
            rs4!id = rs1!id
            rs4!County = "ZZ-State"
            rs4!Members = DSum("CustomerCount", "Report", "ID='" & rs1!id & "' AND CustomerState NOT IN " & strState)
            rs4!Visits = DSum("VisitCount", "Report", "ID='" & rs1!id & "' AND CustomerState NOT IN " & strState)
            rs4!BillAmt = DSum("BillAmount", "Report", "ID='" & rs1!id & "' AND CustomerState NOT IN " & strState)
            rs4.Update
        End Select
        rs1.MoveNext
    Loop
    rs1.Close
    rs2.Close
    rs3.Close
    rs4.Close
End Sub

[NTC]

At a quick glance of your requirements - they appear to be able to be accomplished using standard queries. There is no need to write vba/sql strings and certainly not looping.

Check out some of the online videos for query design using the query design view feature.

[Minty]

You can almost certainly achieve this using aggregate queries and joins.
Anytime you see Domain functions used to update values in a query they could normally be achieved by making that an aggregate query and joining it to provide the results.

I suspect if you provided some real sample data and an example of the output this could be resolved easily.
Make a stripped down version of your data, remove any sensitive info but leave enough to provide a couple of the desired outputs. Compact and repair it then zip it to post it up here.

[Euler271]

Forum.zip

Here is my zipped file. (I hope I did this right.)
There are three tables: Report, T2, ZZ
Report contains the raw data. T2 contains the information for the two companies (ID's 1 and 2).
ZZ contains the desired output.
If a company has customers in the county in which it's located then all the member information from that county goes into that county's bucket.
If a company has customers outside of any counties in which it's located within the same state then that data goes into the ZZCOUNTY bucket.
If a company has locations in more than one state and has customers outside of the states in which it's located then that customer data goes into the ZZSTATE bucket.
If a company has one location in a state then all of the customer information for that state goes into that location's bucket.

Please reply with any questions or help with the SQL statements. Thank you all very much.

[Minty]

Paste this into a query SQL window then switch to design view to see how it works;

SELECT Report.ID, Sum(Report.VisitCount) AS SumOfVisitCount, Sum(Report.BillAmount) AS SumOfBillAmount, "ZZCounty" AS Pot 
FROM Report LEFT JOIN T2 ON (Report.CustomerState = T2.CompanyState) AND (Report.ID = T2.ID)
WHERE (((T2.CompanyState) Is Null))
GROUP BY Report.ID, "ZZCounty";

This should get you headed in the right direction.