Closest office

[JCRMet]

I have a table of zip codes (about 42k of them) and a table of offices (500 or so) we work with. I want to, through a lat/lon calculation, update a field called 'closest office' in the zip code table so that I can identify the closest office for each zip. I have the lat/lon calculation, but I don't know how to do the joins to get the query to calcuate the distance for all the offices and return the office with the minimum distance. No matter how I try to join the zips in the two tables, it only returns the matches.

 

[JoJ]

Your problem might be better solved by using VBA code.

Imagine a nested loop where your ZIP table is the outer loop and your Office table in the inner loop. In the inner loop, calculate the distances of each office and with an IF statement store the closest distance and update your closest office field.

 

[JCRMet]

I've done quite a bit of work in VBA with custom functions, but they are generally for calling the function within in a query where all the Joins are done. You're suggesting I execute the whole process in VBA? What would the statement be for calling the tables? I have two - ZIPSCOORDS and Districts.

 

[JoJ]

I can't think of a way to solve your problem with queries. But yes, try the whole process in VBA.

Here, try this for starters:

Dim rstClient As New ADODB.Recordset
Dim rstOffice As New ADODB.Recordset

rstClient.Open "Client", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
rstOffice.Open "Office", CurrentProject.Connection, adOpenDynamic, adLockReadOnly

Do While Not rstClient.EOF
        
    Do While Not rstOffice.EOF
        
        If [Closer than previous] Then
            
            rstClient.Fields("ClosestOffice") = rstOffice.Fields("OfficeAddress")
            rstClient.Update
            
        End If
        
        rstOffice.MoveNext
    Loop
    
    rstOffice.MoveFirst
    rstClient.MoveNext
    
Loop

 

[JCRMet]

Thanks. I worked off your suggestion and made changes to fit. Worked perfectly. I appreciate the help. New skill learned, too.