find out first higher /lower among the high /lows

[cdsmumbai]

I have the following the data in access 2003 database.

sr No. high low pline dline basetype zonetype
1 1146 1142 1124 1126 1 1
2 1136 1134 1128 1126 1 2
3 1137 1130 1129 1125 1 2
4 1147 1139 1131 1134 1 2
5 1131 1128 1128 1130 1 1
6 1135 1131 1129 1132 1 1
7 1138 1130 1132 1134 1 1
8 1149 1127 1134 1131 1 2
9 1147 1129 1136 1132 1 1


Now I wnat to find out first maximum high for each sr.no with zonetype 2 and first maximum low for each sr.no. with zonetype 1. for eg :
sr.No. 1 with zonetype 1 first low after the current record would be 1130 because next record low is 1139 which is greater than 1130 and of course in next record low is 1128 which will be ignored for sr.NO.1 since the first low is 1130 before increase in the low no. Similarly for sr.No.2 with zonetype 2, first high after the current record would be 1147 because in next record high is 1131 which is lower than 1147 and subsequent high of 1149 in sr.no.8 will be ignored and so on. Hence, my desired output will be

sr.No. zonetype result difference in sr.no.
1 1 1130 1-3=2
2 2 1147 2-4=2
3 2 1147 3-4=1
4 2 1149 4-8=4
5 1 1127 5-8=3
6 1 1127 6-8=2
7 1 1127 7-8=1
8 2 1147 8-9=1

I have the following vba, which is not giving the desired output. Can u help me correcting the vba. thanks in advance

Sub calMHIGHnN6()
Dim mydb As DAO.Database
Dim myRS As DAO.Recordset
Dim myCounter As Double
Dim mycurrenthigh As Double
Dim myprevioushigh As Double
Dim mycurrentlow As Double
Dim mypreviouslow As Double
Dim mydz As Integer
Dim mybase As Integer
Set mydb = CurrentDb
Set myRS = mydb.OpenRecordset("query14")

' Loop through all records in table
myRS.MoveLast
myRS.MoveFirst
Do While Not myRS.EOF

mydz = myRS("zonetype")
mybase = myRS("basetype")
mycurrentcd1 = myRS("pline")
mycurrentcd2 = myRS("dline")
mycurrenthigh = myRS("high")
mycurrentlow = myRS("low")


period_count = 0
' Check to see mfirst maximum high/low

If mydz = 2 Then
If mycurrenthigh > myprevioushigh Then
myCounter = mycurrenthigh
Else
If mycurrenthigh < myprevioushigh Then
myRS.MoveNext
myCounter = mycurrentcd1
End If
End If

If mydz = 1 Then

If mycurrentlow < mypreviouslow Then
myCounter = mycurrentlow

Else
If mycurrentlow > mypreviouslow Then
myRS.MoveNext

End If
End If
End If
End If

' Assgin counter value to the result
myRS.Edit
myRS.Fields("result") = myCounter

myRS.Update
' Assign current number to previous number and go to next record
myRS.MoveNext

Loop

' Close recordset
myRS.Close
mydb.Close
Set myRS = Nothing
Set mydb = Nothing

MsgBox "Done!"
End Sub

[amrut]

Now I wnat to find out first maximum high for each sr.no with zonetype 2 and first maximum low for each sr.no. with zonetype 1.

I think subquery would be a better option than VBA
See here for use of subquery http://allenbrowne.com/subquery-01.html

[ranman256]

Max/Mins can be tricky. I'd create 1 query for each column...
qsMaxMinDline,
qsMaxMinPLine,
etc.
or append them to a table for the results.