Max Function

[oggsta]

Hello

I have 4 fields each with a number between 1-15 in the field.

I would like to create a additional field that tells automatically tells me what the highest number is. I.e. if I entered the numbers

The field names are

[score]
[score1]
[score2]
[score3]

If i.e. entered 6,8, 2, 9

The additional field would generate the number 9 automatically. I have tried using the maximum function but it does not work.

I have entered the following in the control source for the additional field
= Max ( [score] And [score1] And [score2] And [score3])

This generates –1 for some reason, I think I may have incorrect syntax

Any help appreciated

 

[thornmastr]

You have to remember that both the max and the Dmax function return the maximum value for a a column on a span of records. Given that each number in your list of numbers is the value of a particular column of a table the SQL you want is something along the order of:

SELECT Max(test.value1) AS MaxOfvalue1 FROM test;

If what you are looking for is a max function which takes a series of user inputs and returns the max value, post back. Somewhere I’ve got a function that does it. However it is for Access higher tha A97 and I’m going to have to find it.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[cmmrfrds]

You can use a combination of Union and Derived Queries to find the max number. It might be more complicated to find which column was the max, but if needed maybe you can modify this query. What the union does, is make each column a separate row and then the max can be applied to the overall results of the derived query which contains these rows.

SELECT max(ut.fld), ut.ID
FROM testtable AS A INNER JOIN [SELECT TestTable.ID, TestTable.field1 as fld
FROM TestTable
Union
SELECT TestTable.ID, TestTable.field2
FROM TestTable
Union
SELECT TestTable.ID, TestTable.field3
FROM TestTable
Union
SELECT TestTable.ID,TestTable.field4
FROM TestTable
]. AS ut ON ut.ID = A.ID
Group by ut.ID

 

[thornmastr]

You have to remember that both the max and the Dmax function return the maximum value for a a column on a span of records. Given that each number in your list of numbers is the value of a particular column of a table the SQL you want is something along the order of:

SELECT Max(test.value1) AS MaxOfvalue1 FROM test;

If what you are looking for is a max function which takes a series of user inputs and returns the max value, post back. Somewhere I’ve got a function that does it. However it is for Access higher tha A97 and I’m going to have to find it.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[cheerio]

Your problem here arises because your data is not normalised. Normalisation is a method of structuring your tables so that queries become simpler and unambiguous and the data in the tables is easier to maintain.

In this case, you should have a separate table with just one score column and have another column with a sequence number 1,2,3 etc. You can then do your max on the one score column.

If you do not have a normalised structure you will find that life is much more complicated. For example, what happens if someone decides there should be a fifth score. With your approach you have to add another column and change all your queries. With mine you just allow the sequence numbers to go up to 5.

Ken

 

[dusanv]

If you really have only 4 columns for which you need to find a highest score, you can use nested IIF statements below:

High Score: IIf(IIf(IIf([MyTable]![score]>[MyTable]![score1],[MyTable]![score],[MyTable]![score1])>[MyTable]![score2],IIf([MyTable]![score]>[MyTable]![score1],[MyTable]![score],[MyTable]![score1]),[score2])>[score3],IIf(IIf([MyTable]![score]>[MyTable]![score1],[MyTable]![score],[MyTable]![score1])>[MyTable]![score2],IIf([MyTable]![score]>[MyTable]![score1],[MyTable]![score],[MyTable]![score1]),[score2]),[score3])

If you haven't done this already, you would first need to create a query that is simply pulling your table fields and paste this code into an additional query field definition (you'll need to replace "Table1" with the real name of your table first though).

Hope this helps,
Dusan

 

[dusanv]

Read that "MyTable" instead of "Table1"...

 

[MichaelRed]

ScoreMax: basMaxVal([Score], [Score1], [Score2], [Score3])

Where (OBVIOUSLY?) ScoreMax is a field in the query which is based on the table which includes the various (other) scores).

Public Function basMaxVal(ParamArray varMyVals() As Variant) As Variant

    'Michael Red 10/25/2001
    'To return the MAXIMUM or a series of values

    Dim Idx As Integer
    Dim MyMax As Variant

    For Idx = 0 To UBound(varMyVals())
        If (IsMissing(varMyVals(Idx))) Then
            GoTo NextVal
        End If
        If (varMyVals(Idx) > MyMax) Then
            MyMax = varMyVals(Idx)
        End If
NextVal:
    Next Idx

    basMaxVal = MyMax

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[thornmastr]

The following is a function that will return the max value for any amount of numbers provided those numbers are passed as an array of longs (single dimension). You can pass four values or 40,000 values ir makes no difference.

The functions that begins with and end with a line of asterisks should go in a separate module. The function funMaxLng is what you call. It can go in any module you wish.

Call like maxval = funmaxlng(myarray)




Public Function funMaxLng(lngArrayin() As Long) As Long: funMaxLng = 0
Dim intLow As Integer
Dim intUpper As Integer
Dim db As Database
Dim rs As Recordset
Dim strSQL As String

Create
intLow = LBound(lngArrayin)
intUpper = UBound(lngArrayin)
Set db = CurrentDb
Set rs = db.OpenRecordset("t1", dbOpenTable)
With rs
For intLow = intLow To intUpper Step 1
.AddNew
!Value = lngArrayin(intLow)
.Update
Next
.Close
End With
strSQL = "SELECT Max(value) AS Maxvalue FROM t1"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
funMaxLng = rs!maxvalue
rs.Close
DeleteTable ("t1&quot
Set rs = Nothing
Set db = Nothing

End Function


Public Sub DeleteTable(ByVal tabName As String)
On Error Resume Next
CurrentDb.TableDefs.Delete tabName
On Error GoTo 0
End Sub

**********************************************************
Option Compare Database
Option Explicit

Const ERR_PROPERTY_NONEXISTENT = 3270
Const MB_YESNOCANCEL = 3
Const MB_QUESTION = 32
Const DOYES = 6
Const DONO = 7
Const DOCANCEL = 2

' ---------------------------------------------------------
' variables
' ---------------------------------------------------------
Dim wspc As Workspace
Dim dtbs As Database
Dim tabl As TableDef
Dim coln As DAO.Field
Dim refr As Relation
Dim dtbName As String
Dim newLine As String
Dim retCode As Long

' ---------------------------------------------------------
' Table exists
' ---------------------------------------------------------
Private Function DoCreateTable(tabName As String) As Integer
Dim n As Integer, resp As Integer

DoCreateTable = DOYES
On Error Resume Next
For n = 0 To dtbs.TableDefs.Count - 1
If dtbs.TableDefs.Name = tabName Then
'resp = MsgBox("The Table '" & tabName & "' already exists in the Database. Do you want to delete it and create again ?", MB_QUESTION + MB_YESNOCANCEL, "Confirmation&quot
resp = DOYES
If resp = DOYES Then
' delete old Table
DeleteATable tabName
DoCreateTable = DOYES
ElseIf resp = DOCANCEL Then
wspc.Rollback
Stop
Else
DoCreateTable = DONO
End If
Exit Function
End If
Next n
End Function

' ---------------------------------------------------------
' Create a Table
' ---------------------------------------------------------
Private Sub CreateATable(tabName As String)
Debug.Print newLine & "Creating Table '" & tabName & "' ..."
On Error Resume Next
Set tabl = dtbs.CreateTableDef(tabName)
retCode = Err
On Error GoTo 0
AccTestError retCode
End Sub

' ---------------------------------------------------------
' Add a Table to the database
' ---------------------------------------------------------
Private Sub AddATable()
On Error Resume Next
dtbs.TableDefs.Append tabl
retCode = Err
On Error GoTo 0
AccTestError retCode
End Sub

' ---------------------------------------------------------
' Add a prop. for a Table
' ---------------------------------------------------------
Private Sub AddTableProp(tabName As String, PropName As String, PropType As Integer, propValue As Variant)
Dim prop As Property

On Error Resume Next
Set tabl = dtbs.TableDefs(tabName)
If Err <> 0 Then
Exit Sub
End If
tabl.Properties(PropName) = propValue
If Err <> 0 Then
If Err = ERR_PROPERTY_NONEXISTENT Then
On Error Resume Next
Set prop = tabl.CreateProperty(PropName, PropType, propValue)
tabl.Properties.Append prop
End If
On Error GoTo 0
End If
End Sub

Private Sub AddAColumn(cnam As String, dttp As Integer, mlen As Long, prec As Integer, isMand As String, rule As String, dval As String, colnNo As Integer, autoInc As String)

Dim intType As Long, intLen As Long, intPrec As Integer

intType = DB_LONG
intLen = 0
intPrec = 0

Debug.Print &quot; Creating Column '&quot; & cnam & &quot;' ...&quot;


Set coln = tabl.CreateField(cnam)
If autoInc = &quot;YES&quot; Then
coln.Attributes = coln.Attributes + DB_AUTOINCRFIELD
End If
coln.Type = dttp
coln.Size = mlen
If LCase(isMand) = &quot;yes&quot; Then
coln.Required = True
End If
coln.ValidationRule = rule
coln.DefaultValue = dval
coln.OrdinalPosition = colnNo
tabl.Fields.Append coln
retCode = Err
On Error GoTo 0
AccTestError retCode
End Sub

' ---------------------------------------------------------
' Add a Property for a Column
' ---------------------------------------------------------
Private Sub AddColumnProp(tabName As String, colName As String, PropName As String, PropType As Integer, propValue As Variant)
Dim prop As Property

On Error Resume Next
Set tabl = dtbs.TableDefs(tabName)
Set coln = tabl.Fields(colName)
If Err <> 0 Then
Exit Sub
End If
coln.Properties(PropName) = propValue
If Err <> 0 Then
If Err = ERR_PROPERTY_NONEXISTENT Then
On Error Resume Next
Set prop = coln.CreateProperty(PropName, PropType, propValue)
coln.Properties.Append prop
End If
On Error GoTo 0
End If
End Sub

' ---------------------------------------------------------
' Create an Index
' ---------------------------------------------------------
Private Sub CreateAnIndex(ByVal prim As String, ByVal uniq As String, ByVal clus As String, ByVal idxName As String, ByVal tabName As String, ByVal colList As String)
Dim idx As Index
Dim idxColumns As String

' delete old Index
DeleteAnIndex idxName, tabName

Debug.Print &quot;Creating Index '&quot; & idxName & &quot;' ...&quot;
On Error Resume Next
Set tabl = dtbs.TableDefs(tabName)
retCode = Err
On Error GoTo 0
AccTestError retCode
Set idx = tabl.CreateIndex(idxName)
idx.Name = idxName
'idx.Fields = colList
If LCase(prim) = &quot;primarykey&quot; Then
idx.Primary = True
End If
' If LCase(forn) = &quot;foreignkey&quot; Then
' idx.Foreign = True
' End If
If LCase(uniq) = &quot;unique&quot; Then
idx.Unique = True
End If
If LCase(clus) = &quot;cluster&quot; Then
idx.Clustered = True
End If
idx.Required = True

idxColumns = colList
Dim commaLoc As Integer
' set the first occurrence of &quot;,&quot;
commaLoc = InStr(idxColumns, &quot;,&quot

Dim colName As String
Dim SortOrder As String
Dim fld
Do While (commaLoc > 0)
colName = Left(idxColumns, commaLoc - 1)

' trim the column names
colName = Trim(colName)

Set fld = idx.CreateField(colName)


On Error Resume Next
idx.Fields.Append fld

' the first column
idxColumns = Mid(idxColumns, commaLoc + 1)
commaLoc = InStr(idxColumns, &quot;,&quot
Loop
If Len(idxColumns) > 0 Then
colName = idxColumns
Set fld = idx.CreateField(colName)
idx.Fields.Append fld
End If


On Error Resume Next
tabl.Indexes.Append idx
End Sub

' ---------------------------------------------------------
' Delete an Index
' ---------------------------------------------------------
Private Sub DeleteAnIndex(ByVal idxName As String, ByVal tabName As String)
On Error Resume Next
dtbs.TableDefs(tabName).Indexes.Delete idxName
retCode = Err
On Error GoTo 0
End Sub

' ---------------------------------------------------------
' Create a Relation
' ---------------------------------------------------------
Private Sub CreateAReference(refrName As String, primTab As String, fornTab As String)
Debug.Print &quot;Creating Relation '&quot; & refrName & &quot;' ...&quot;
On Error Resume Next
Set refr = dtbs.CreateRelation(refrName)
refr.Table = primTab
refr.ForeignTable = fornTab
End Sub

' ---------------------------------------------------------
' Add joint in Relation
' ---------------------------------------------------------
Private Sub AddARefrCol(refrName As String, primKey As String, fornKey As String)
Dim fld As DAO.Field

On Error Resume Next
Set fld = refr.CreateField(primKey)
fld.ForeignName = fornKey
refr.Fields.Append fld
End Sub

' ---------------------------------------------------------
' Add current Relation
' ---------------------------------------------------------
Private Sub AddAReference(refrName As String)
On Error Resume Next
dtbs.Relations.Append refr
End Sub

' ---------------------------------------------------------
' Display err mess
' ---------------------------------------------------------
Private Sub AccTestError(ret As Long)
If ret = 0 Then Exit Sub
If ret < 0 Then ret = -ret

Debug.Print newLine & &quot;Error : &quot; & Error$(ret) & &quot;.&quot;

' stop this module
Debug.Print newLine & &quot;Database not successfully created.&quot;
'wspc.Rollback
Stop
End Sub


Sub Create()
dtbName = &quot;Not yet specified&quot;
newLine = Chr(13) & Chr(10)
On Error Resume Next
Set wspc = DBEngine.Workspaces(0)
Set dtbs = wspc.Databases(0)
retCode = Err
On Error GoTo 0
AccTestError retCode
wspc.BeginTrans

Debug.Print
Debug.Print &quot;------------------------------------------------------&quot;
Debug.Print &quot; Creating the Database '&quot;; dtbName; &quot;'&quot;
Debug.Print &quot; in the file &quot;; dtbs.Name
Debug.Print &quot;------------------------------------------------------&quot;
Debug.Print



If DoCreateTable(&quot;t1&quot = DOYES Then
CreateATable &quot;t1&quot;
AddAColumn &quot;pk&quot;, DB_LONG, 0, 0, &quot;YES&quot;, &quot;&quot;, &quot;&quot;, 1, &quot;YES&quot;
AddAColumn &quot;value&quot;, DB_LONG, 0, 0, &quot;YES&quot;, &quot;&quot;, &quot;&quot;, 2, &quot;NO&quot;
AddATable
End If


CreateAnIndex &quot;primarykey&quot;, &quot;unique&quot;, &quot;&quot;, &quot;PK_t1&quot;, &quot;t1&quot;, &quot;pk&quot;
CreateAnIndex &quot;&quot;, &quot;unique&quot;, &quot;&quot;, &quot;UNIQUE_t1_1&quot;, &quot;t1&quot;, &quot;pk&quot;

' Commit transaction
wspc.CommitTrans

' End of program
Debug.Print newLine & &quot;Database has been successfully created.&quot;
' MsgBox &quot;Database has been successfully created.&quot;, MB_YESNOCANCEL, &quot;Message&quot;
'End
End Sub

************************************************************************
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com