Max again

  • Thread starter Thread starter ooxx
  • Start date Start date
O

ooxx

Hi,

I am really shy to ask this question again since then from subject "Max no
group" .
You can refer to sample data.

SELECT *
FROM myTable
WHERE f3= ( SELECT MAX(f3)
FROM myTable as X
WHERE X.F1 = MyTable.X1 )

As I have applied this to my form code as shown below:
It was to much help for me, Thanks.

CurrentDb().Execute _
"Update myTable Set f3 = 99 WHERE f3 = (SELECT MAX(f3) FROM myTable as X
WHERE X.F1 = '" & Me.T1 & "')"

This is the code and it was on me in finding max value of "f3" to update
record of "f3" according to "T1" control textbox.

By the way, I 'd like to have the code to find the second max value or third
of "f3" with only single code. hehehe... I try that but it took me too much
storage of text code inside. I'd like short.

Anybody help?
Thanks,
 
A while back, I created a function (similar to DLOOKUP) that will identify
the Nth largest value (distinct values). It is pretty self explanitory.

You pass it the field name, table name, which value (N) and an optional
critieria. It returns a variant to allow it to return NULL values, which it
does if:
a. the query returns no results
b. there are less than N distinct values in the field


Public Function fnNthLargestValue(FieldName As String, TableName As String, _
N As Integer, Optional Criteria As Variant
= Null) As Variant

Dim strSQL As String
Dim rs As DAO.Recordset
Dim intLoop As Integer

strSQL = "SELECT Top " & N & " [" & FieldName & "] " _
& "FROM [" & TableName & "] " _
& ("WHERE " + Criteria + " ") _
& "GROUP BY [" & FieldName & "] " _
& "ORDER BY [" & FieldName & "] DESC"

Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
If rs.EOF Then
fnNthLargestValue = Null
Else
rs.MoveLast
If rs.RecordCount < N Then
fnNthLargestValue = Null
Else
rs.MoveFirst
For intLoop = 1 To N - 1
rs.MoveNext
Next
fnNthLargestValue = rs(0)
End If
End If
rs.Close
Set rs = Nothing

End Function

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
Hi,

It 's neat. Great Function. I like it.

Thanks,

Dale Fye said:
A while back, I created a function (similar to DLOOKUP) that will identify
the Nth largest value (distinct values). It is pretty self explanitory.

You pass it the field name, table name, which value (N) and an optional
critieria. It returns a variant to allow it to return NULL values, which it
does if:
a. the query returns no results
b. there are less than N distinct values in the field


Public Function fnNthLargestValue(FieldName As String, TableName As String, _
N As Integer, Optional Criteria As Variant
= Null) As Variant

Dim strSQL As String
Dim rs As DAO.Recordset
Dim intLoop As Integer

strSQL = "SELECT Top " & N & " [" & FieldName & "] " _
& "FROM [" & TableName & "] " _
& ("WHERE " + Criteria + " ") _
& "GROUP BY [" & FieldName & "] " _
& "ORDER BY [" & FieldName & "] DESC"

Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
If rs.EOF Then
fnNthLargestValue = Null
Else
rs.MoveLast
If rs.RecordCount < N Then
fnNthLargestValue = Null
Else
rs.MoveFirst
For intLoop = 1 To N - 1
rs.MoveNext
Next
fnNthLargestValue = rs(0)
End If
End If
rs.Close
Set rs = Nothing

End Function

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



ooxx said:
Hi,

I am really shy to ask this question again since then from subject "Max no
group" .
You can refer to sample data.

SELECT *
FROM myTable
WHERE f3= ( SELECT MAX(f3)
FROM myTable as X
WHERE X.F1 = MyTable.X1 )

As I have applied this to my form code as shown below:
It was to much help for me, Thanks.

CurrentDb().Execute _
"Update myTable Set f3 = 99 WHERE f3 = (SELECT MAX(f3) FROM myTable as X
WHERE X.F1 = '" & Me.T1 & "')"

This is the code and it was on me in finding max value of "f3" to update
record of "f3" according to "T1" control textbox.

By the way, I 'd like to have the code to find the second max value or third
of "f3" with only single code. hehehe... I try that but it took me too much
storage of text code inside. I'd like short.

Anybody help?
Thanks,
Click to expand...
Click to expand...
 
Hi,

In this case, I 'd like to know the min value. I've notice the "top" , so
what would be if it would be the min value. I know you're waiting for this
question,if my wonder was correct.

Thanks,

Dale Fye said:
A while back, I created a function (similar to DLOOKUP) that will identify
the Nth largest value (distinct values). It is pretty self explanitory.

You pass it the field name, table name, which value (N) and an optional
critieria. It returns a variant to allow it to return NULL values, which it
does if:
a. the query returns no results
b. there are less than N distinct values in the field


Public Function fnNthLargestValue(FieldName As String, TableName As String, _
N As Integer, Optional Criteria As Variant
= Null) As Variant

Dim strSQL As String
Dim rs As DAO.Recordset
Dim intLoop As Integer

strSQL = "SELECT Top " & N & " [" & FieldName & "] " _
& "FROM [" & TableName & "] " _
& ("WHERE " + Criteria + " ") _
& "GROUP BY [" & FieldName & "] " _
& "ORDER BY [" & FieldName & "] DESC"

Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
If rs.EOF Then
fnNthLargestValue = Null
Else
rs.MoveLast
If rs.RecordCount < N Then
fnNthLargestValue = Null
Else
rs.MoveFirst
For intLoop = 1 To N - 1
rs.MoveNext
Next
fnNthLargestValue = rs(0)
End If
End If
rs.Close
Set rs = Nothing

End Function

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



ooxx said:
Hi,

I am really shy to ask this question again since then from subject "Max no
group" .
You can refer to sample data.

SELECT *
FROM myTable
WHERE f3= ( SELECT MAX(f3)
FROM myTable as X
WHERE X.F1 = MyTable.X1 )

As I have applied this to my form code as shown below:
It was to much help for me, Thanks.

CurrentDb().Execute _
"Update myTable Set f3 = 99 WHERE f3 = (SELECT MAX(f3) FROM myTable as X
WHERE X.F1 = '" & Me.T1 & "')"

This is the code and it was on me in finding max value of "f3" to update
record of "f3" according to "T1" control textbox.

By the way, I 'd like to have the code to find the second max value or third
of "f3" with only single code. hehehe... I try that but it took me too much
storage of text code inside. I'd like short.

Anybody help?
Thanks,
Click to expand...
Click to expand...
 
Hi ,

I can change that to "asc" , Thanks to the thread.



ooxx said:
Hi,

In this case, I 'd like to know the min value. I've notice the "top" , so
what would be if it would be the min value. I know you're waiting for this
question,if my wonder was correct.

Thanks,

Dale Fye said:
A while back, I created a function (similar to DLOOKUP) that will identify
the Nth largest value (distinct values). It is pretty self explanitory.

You pass it the field name, table name, which value (N) and an optional
critieria. It returns a variant to allow it to return NULL values, which it
does if:
a. the query returns no results
b. there are less than N distinct values in the field


Public Function fnNthLargestValue(FieldName As String, TableName As String, _
N As Integer, Optional Criteria As Variant
= Null) As Variant

Dim strSQL As String
Dim rs As DAO.Recordset
Dim intLoop As Integer

strSQL = "SELECT Top " & N & " [" & FieldName & "] " _
& "FROM [" & TableName & "] " _
& ("WHERE " + Criteria + " ") _
& "GROUP BY [" & FieldName & "] " _
& "ORDER BY [" & FieldName & "] DESC"

Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
If rs.EOF Then
fnNthLargestValue = Null
Else
rs.MoveLast
If rs.RecordCount < N Then
fnNthLargestValue = Null
Else
rs.MoveFirst
For intLoop = 1 To N - 1
rs.MoveNext
Next
fnNthLargestValue = rs(0)
End If
End If
rs.Close
Set rs = Nothing

End Function

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



ooxx said:
Hi,

I am really shy to ask this question again since then from subject "Max no
group" .
You can refer to sample data.

SELECT *
FROM myTable
WHERE f3= ( SELECT MAX(f3)
FROM myTable as X
WHERE X.F1 = MyTable.X1 )

As I have applied this to my form code as shown below:
It was to much help for me, Thanks.

CurrentDb().Execute _
"Update myTable Set f3 = 99 WHERE f3 = (SELECT MAX(f3) FROM myTable as X
WHERE X.F1 = '" & Me.T1 & "')"

This is the code and it was on me in finding max value of "f3" to update
record of "f3" according to "T1" control textbox.

By the way, I 'd like to have the code to find the second max value or third
of "f3" with only single code. hehehe... I try that but it took me too much
storage of text code inside. I'd like short.

Anybody help?
Thanks,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top