Is there a way to find the top n values in a table?
I know I can find the top n in a field, but how can I search the entire table to find the top n values in the entire table?
Each field in the table could have the same values as the other fields
Since a table is comprised of fields, it'd be helpful if you'd provide an example of what you're trying to do. Please include field info (name, data type, etc..)
You would probably need to use a Top N query with a descending sort order in the pertinent field. For instance, if you have a table, Mytable, with fields called nameID, name, and salary, then you would create the following query to find the records with the top salaries.
"SELECT TOP 3 * FROM MyTable ORDER BY salary DESC;"
This will return at least three records with your criteria. Notice I said "at least", because if the third record has the same salary as other records, ALL of the records with that salary will show up. In that case, you need a secondary sort criteria to use to help whittle down until you actually have three records.
I can see what the problem is. It is that the table holds no "values", but instead holds objects (fields) that contain values. So I do not believe this will work the way I wanted to do it.
My idea now is to Iterate through the 100 fields in the table using a select statement to find the max value for each field; then writing the results of the 100 iterations to a table or query.
Here's what I have so far: but Im not sure how to "collect" the values and put them in a table without creating a new record for each entry.
Code:
For fldcnt = 0 To 100
For Each fld In CurrentDb.TableDefs("MyMatrix")
strSQL = "Select MyNumber, Max(" & fldcnt & "From MyMatrix"
Next fld
Next fldcnt
I figure this will select the max number for each field, but I know from what I have, that it will not work because Each iteration is going to add "Mynumber" and the current max value to the table. (Field names are numbered 1 to 100 - hense the iteration from 1 to 100).
But I still cannot seem to figure out how to get max, then the next values, then the next value etc for each field.
Any suggestions/Hints towards a resolution would be great..
I have been working on this but still cannot find a way to get it done.
Here is the code I am using:
Code:
Dim rs As DAO.Recordset
Dim db As Database
Dim strDta As String
Dim fld As DAO.Field
Dim fldcnt As Integer
Dim tbf As DAO.TableDef
Dim strTblName As String
strTblName = "MyMatrix"
Set tbf = db.TableDefs(strTblName)
strDta = ""
For fldcnt = 1 To 100
For Each fld In db.TableDefs(strTblName).Fields
Set rs = CurrentDb.OpenRecordset("Select Max(" & fldcnt & "From MyMatrix")
Do While Not rs.EOF
If rs(fldcnt) <> "" Then
strDta = rs(fldcnt)
Else
rs.Edit
rs(fldcnt) = strDta
rs.Update
End If
rs.MoveNext
Loop
Next fld
Next fldcnt
rs.Close
I can't seem to get by the line:
Code:
Set tbf = db.TableDefs(strTblName)
I get an error saying "Object variable or with block variable not set" and this line is selected in debug.
When I exclude this line and use the tablename in the for clause
Code:
For Each fld In db.TableDefs("MyMatrix").Fields
I get the same error.
I cannot find where this runtime error is even coming from.
I was able to get by the error above (I did not set the db=currentdb.
Can anyone suggest what may be wrong with this code? I keep getting the message that the item not found in this collection.
Table has data
Field names are numbers 1-100
Code:
Dim rs As DAO.Recordset
Dim db As Database
Dim strDta As String
Dim fld As DAO.Field
Dim fldcnt As Integer
Dim tbf As DAO.TableDef
Dim strTblName As String
Dim fldnme As String
Set db = CurrentDb
strTblName = "MyMatrix"
Set tbf = db.TableDefs(strTblName)
strDta = ""
For fldcnt = 1 To 100
For Each fld In db.TableDefs(strTblName).Fields
strSQL = "Select Max(mymatrix.[" & fldcnt & "]) From MyMatrix"
Set rs = CurrentDb.OpenRecordset(strSQL) '"Select Max(" & fldcnt & ") From MyMatrix")
MsgBox strSQL
With rs
Do While Not rs.EOF
fldnme = Trim(Str(fldcnt))
If rs(fldnme) <> "" Then 'Message happens here
strDta = rs(fldnme)
Else
rs.Edit
rs(fldnme) = strDta
rs.Update
End If
rs.MoveNext
Loop
End With
Next fld
Next fldcnt
rs.Close
Dim rs As DAO.Recordset
Dim db As Database
Dim strDta As String
Dim fld As DAO.Field
Dim fldcnt As Integer
Dim tbf As DAO.TableDef
Dim strTblName As String
[COLOR="Red"]Dim fldnme As String[/COLOR] [COLOR="Blue"]([I][B]FIELD[/B][/I])[/COLOR]
Set db = CurrentDb
strTblName = "MyMatrix"
Set tbf = db.TableDefs(strTblName)
strDta = ""
For fldcnt = 1 To 100
For Each fld In db.TableDefs(strTblName).Fields
strSQL = "Select Max(mymatrix.[" & fldcnt & "]) From MyMatrix"
Set rs = CurrentDb.OpenRecordset(strSQL) '"Select Max(" & fldcnt & ") From MyMatrix")
MsgBox strSQL
With rs
Do While Not rs.EOF
fldnme = Trim(Str(fldcnt))
If rs[COLOR="Red"](fldnme)[/COLOR] <> "" Then [COLOR="red"]<--- "fldnme" is a string declaration, not a field dec.[/COLOR]
strDta = rs(fldnme)
Else
rs.Edit
rs(fldnme) = strDta
rs.Update
End If
rs.MoveNext
Loop
End With
Next fld
Next fldcnt
rs.Close
It sure would have helped if you had posted your table structure and
a nice description of what you expected to retrieve.
I take it that you have repeating fields in your table.
If you want to find the max for all columns within a table, there is
no easy answer. If there is a "max" value that's important accross
all of the fields, then all of those fields (columns) should be stored
in a child table.
And, as a "one-time" thing, you needed to know the max value for any of the
fields, you can resort to code.
Code:
Dim rst As DAO.RecordSet
Dim TheMax As Double
Dim i As Long
Set rst = CurrentDb.OpenRecordset("Select * From tblDifferent")
TheMax = 0
While Not rst.EOF And Not rst.BOF
For i = 1 To rst.Fields.Count - 1
If CDbl(rst.Fields(i)) > TheMax Then TheMax = CDbl(rst.Fields(i))
Next i
rst.MoveNext
Wend
Debug.Print "The maximum # is " & TheMax
If you have to know which product incurred the max value, you could also do
something like:
Dim rst As DAO.RecordSet
Dim i As Long
Set rst = CurrentDb.OpenRecordset("Select * From tblDifferent")
TheMax = 0
While Not rst.EOF And Not rst.BOF
For i = 1 To rst.Fields.Count - 1
DoCmd.RunSQL "Insert into tblTempStorage (ProductName, SomeCost) " & _
"Values (" & rst.Fields(0) & ", " & rst.Fields(i) & ")"
Next i
rst.MoveNext
Wend
Then, you can query with:
Select Top 3 ProductName, SomeCost
From tblTempStorage
Order By SomeCost Desc
