Comparing data in diff fields
In Generals Section:
'user defined type
Public Type tVal
recID as Long
recVal as Integer '(or whatever your data type is)
end Type
'Array of defined type
Public arVal() as tVal
Public Sub FindSmallestValue()
Dim rsRec as ADODB.Recordset
Dim iIDX as Integer
Dim iVal as Integer
'open your table as a recordset
Set rsRec = New ADODB.Recordset
rsRec.Open "SELECT * FROM myTable" , MyConnection
'scan records to determin smallest value for each
If Not rsRec.EOF Then
iIDX = 1
Do Untill rsRec.EOF
iVal = rsRec!Field_1
If rsRec!Field_2 < iVal Then iVal = rsRec!Field_2
If rsRec!Field_3 < iVal Then iVal = rsRec!Field_3
ReDim Preserve arVal(iIDX)
arVal(iIDX).recID = rsRec!Field_ID
arVal(iIDX).recVal = iVal
iIDX = iIDX + 1
rsRec.MoveNext
Loop
End If
Set rsRec = Nothing
'update Field_4 based on results in array
For iIDX=1 to Ubound(arVal)
MyConnection.Execute "UPDATE MyTable SET Field_4=" &
arVal(iIDX).recVal & " WHERE Field_ID=" & arValiIDX).recID
Next
End Sub
Quote:
>-----Original Message-----
>I'm struggling with a poorly designed table of my own
>creation, but it's too late to change the design as it
has
>already been distributed to other offices where the data
>collection is performed. I have a table with several
>fields containing serial numbers. I need to select the
>smallest serial number for each record and copy that
value
>to yet another field, via VBA or an update query.
>Any thoughts?
