VBA Question

[tmcneil]

All,

I have an Access database with a table of 442 airports, my company's maintenance centers (MC's) and distances (in miles). This output table was created by measuring the as-the-crow-flies distance from all 442 airports to each of the eleven MC's. The distance of each airport is calculated to each MC, so, I am left with a table of 4862 records.

I would like to write code to go through the table, select out all of the records for each Iata code and then select the minimum distance to store in a resultant table. I would like to do all of this on the fly until the last record is read. My output will have 442 airports with the distance to its closest MC.

I have written two queries to help push myself along. The first, choose any Iata code, as well as, the MC and distance fields. 2. Select the minimum distance value of all 11 records. I think this is a good start, but how do I automate this.

Thanks,
Todd McNeil
ARINC
Annapolis, MD

 

[kraxmo]

Dear tmcneil,

There are a couple of different ways to slay this dragon. You could join your queries together and output them via an append table query or write code to do this via cursor processing.

Assumption:

Table tblAirportMCDistance:
- IATA Text
- MC Text
- Distance Number Single

Table tblAirportClosestMC:
- IATA Text
- MC Text
- Distance Number Single

#1: Query Approach

Query qryAirportMinMC:

SELECT IATA, Min(Distance) AS MinOfDistance
FROM tblAirportMCDistances
GROUP BY IATA
ORDER BY IATA

Query qryAirportClosestMC:

INSERT INTO tblAirportPrimaryMC ( IATA, MC, Distance )
SELECT tblAirportMCDistances.IATA, tblAirportMCDistances.MC, tblAirportMCDistances.Distance
FROM tblAirportMCDistances INNER JOIN qryAirportMinMC ON (tblAirportMCDistances.Distance = qryAirportMinMC.MinOfDistance) AND (tblAirportMCDistances.IATA = qryAirportMinMC.IATA)
ORDER BY tblAirportMCDistances.IATA;

Create a macro that will open the qryAirportClosestMC and viola!!!

#2: Cursor Approach

Create module a module with the following function:

Function gintMakeShort() As Integer
Dim cn As ADODB.Connection
Dim rsOld As ADODB.Recordset
Dim rsNew As ADODB.Recordset
Dim strIATA As String
Dim strMC As String
Dim sngDistance As Single

' Delete existing data
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM tblAirportClosestMC"
DoCmd.SetWarnings True

' Setup the recordset attributes
Set cn = CurrentProject.Connection
Set rsOld = New ADODB.Recordset
Set rsNew = New ADODB.Recordset

' Old = table sorted by IATA, Distance, MC
rsOld.ActiveConnection = cn
rsOld.Source = "SELECT * FROM tblAirportMCDistances ORDER BY IATA, Distance"
rsOld.Open

' New = table with minimum IATA MC's
rsNew.ActiveConnection = cn
rsNew.Source = "tblAirportPrimaryMC"
rsNew.LockType = adLockOptimistic
rsNew.CursorType = adOpenDynamic
rsNew.Open

' Save data
strIATA = rsOld!IATA
strMC = rsOld!MC
sngDistance = rsOld!Distance
Do Until rsOld.EOF
' If Airport changes
If strIATA <> rsOld!IATA Then
Debug.Print strIATA, strMC, sngDistance
' Insert new airport mc distance
rsNew.AddNew
rsNew!IATA = strIATA
rsNew!MC = strIATA
rsNew!Distance = sngDistance
rsNew.Update

strIATA = rsOld!IATA
strMC = rsOld!MC
sngDistance = rsOld!Distance
End If

rsOld.MoveNext
Loop

' Write last one
Debug.Print strIATA, strMC, sngDistance
rsNew.AddNew
rsNew!IATA = strIATA
rsNew!MC = strIATA
rsNew!Distance = sngDistance
rsNew.Update

' Cleanup the objects
rsOld.Close
rsNew.Close

Set rsOld = Nothing
Set rsNew = Nothing
End Function

To execute this puppy, press Ctrl-G anywhere and type the following and press Enter:

? gintMakeShort()


Have fun with these solutions!

kraxmo Spinning gold into straw...