max no group

[Guest]

Hi ,

Is there any solution for Max without grouping?
There are different data and only one distinct on the row to display for the
max value. For example, data are as : 1, 2 , 6 , 7 and another column to
find for max value here is for relation to it : 3, 4, 9, 2 so the only
max of them is 6, 9 . I try it on the expression of max but there is no
other option on another to find relation of it of the column expression max.
Thanks any idea?

 

[Michel Walsh]

If there is no GROUP, the MAX occur over the column for all the records of
the table.


If your data is like:

f1 f2
1 3
2 4
6 9
7 2



and you wish to get the record where the max(f2) occurs, then something
like:


SELECT *
FROM myTable
WHERE f2= ( SELECT MAX(f2)
FROM myTable )


would return

6 9



If you want the max of each column, it is simply:

SELECT MAX(f1), MAX(f2)
FROM myTable




Vanderghast, Access MVP

 

[John Spencer]

If you don't have to many fields, you can use a union query to put the data
into the proper format and then get the max.

SELECT PrimaryKeyField, F1
FROM YourTable
UNION ALL
SELECT PrimaryKeyField, F2
FROM YourTable
UNION ALL
SELECT PrimaryKeyField, F3
FROM YourTable

Now with that you can use
SELECT PrimaryKeyField, Max(F1) as RowMax
FROM SavedUnionQuery.

If you have less than 30 fields, you can use a VBA function to return the
max for the row
SELECT MaxRow(Field1, field2, field3, field4) as LargestValue
, Field1
, Field2
, Field3
, Field4
FROM YourTable

Code Function follows - paste it into a VBA module and save it. Then you
can call it as needed

'==========================================
Public Function MaxVal(ParamArray MyArray()) As Variant
'Dale Fye's Version
Dim varMax As Variant
Dim intLoop As Integer
varMax = Null

For intLoop = LBound(MyArray) To UBound(MyArray)
If IsNull(MyArray(intLoop)) Then
'do nothing
ElseIf IsNull(varMax) Or MyArray(intLoop) > varMax Then
varMax = MyArray(intLoop)
End If
Next
MaxVal = varMax

End Function
'=============== End Code ====================

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

hi,

Thanks for both Your great helping.

By the way, if I have one extra column as to do need the grouping to make
classifcation, for example, another text Field name "TxtField" to find the
max value of each of them in TxtField. I would prefer the result : row1: A,
2, 10 row2 : B, 7, 11 row3: C, 3, 12 ... while the last column or F3 is the
max value.
Thanks

 

[John Spencer]

Sorry. I Don't understand your question.

Perhaps if you post a couple sample records and the results you want to
see, the problem will be clearer to me and others.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Michel Walsh]

Since SELECT * select all the fields, and since the field to find the max if
f3, instead of f2, that was not trivial? Try:


SELECT *
FROM myTable
WHERE f3= ( SELECT MAX(f3)
FROM myTable )





Vanderghast, Access MVP

 

[Guest]

Hi ,

Let show the sample data :

F1,F2,F3
A,2,1
A,1,5
B,2,6
B,3,0
B,4,2
C,3,7
C,2,1
C,4,0
A,3,2

and the sample result follows:

F1,F2,F3
A,1,5
B,2,6
C,3,7

Yes, that is to find max values 5, 6, 7 according the group of each max

Thanks for help , any idea?

 

[John Spencer]

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

If you don't need to update the data and your table and field names don't
require brackets - names consist of only letters, numbers, and underscore
character - then you can use something like the query below which is faster
than the coorelated query above. If your data set is small and properly
indexed then there is probably no detectable difference in the speed of the
two queries.

SELECT MyTable.*
FROM MyTable INNER JOIN
(SELECT F1, Max(F3) as TheMax
FROM MyTable
GROUP BY F1) as qMax
ON MyTable.F1 = qMax.F1
and MyTable.F3 = qMax.TheMax
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Hi Thanks,

I don't know how to do the max again, but it is the second of its values.
That is Max the second. Can the Access find the result of as follows? I
wonder>.

A,3,2
B,4,2
C,2,1

That is the second max to have sql compare to the max value of F3 and find
out the second related to show.

Thanks

 

[Michel Walsh]

You can rank by group. A possible solution:

SELECT a.f1, a.f2
FROM myTable AS a INNER JOIN myTable AS b
ON a.f1=b.f1 AND a.f2 >= b.f2
GROUP BY a.f1, a.f2
HAVING COUNT(*) = 2


If you want the 2 max, use:

HAVING COUNT(*) <= 2


and the 3 max values of f2, for each value (group of) f1:

HAVING COUNT(*) <= 3

That assumes that f2 has no duplicated values, for a given f1.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Hi,

Thanks. Is it possible to bookmark record according to the SQL? Since the
bookmark is required to clone database, can we avoid the method cloning
database?

If not, is there possible other method to locate the record according to the
SQL? Can we locate record according to the SQL view?

For example, if we conditioning our SQL by MAX value of F3, and we need to
locate this SQL F1 = me.TxtFind, then we can substitue the value in F2, F3
with parameters.

Thanks,

 

[Michel Walsh]

There is no bookmark, as far as SQL is concerned. To have a bookmark, you
probably refer to a recordset. Looping through a recordset is a VBA
alternative mostly unrelated to SQL, which is not often preferable to SQL.
What is wrong with the five small lines of SQL?


Vanderghast, Access MVP

 

[Guest]

Hi, Thanks

No nothing Thanks, only I get limited usage of SQL now. I hope that
something should be applied with SQL with VBA to find the max value. If SQL
could not be applied to locate record in VBA, then what would be. If I need
to locate and add new record from the previous data stored ready in MyTable.

I try the code below:

Set lodb = CurrentDb()

Set lorst = lodb.OpenRecordset("MyTable")

lorst.MoveFirst

Do Until lorst.EOF
With lorst
If Len(!F1) = me.TxtF1 Then
X2 = me.TxtnewValue_F2
X3 = me.TxtnewValue_F3
.AddNew
!F1 = me.TxtF1
!F2 = X2
!F3 = X3
.Update

End If
.MoveNext
End With
Loop

In fact, I get limited knowledge how to move record back by 1 at a time, and
then I need to retrieve the stored data from the Max value of F3, and then
calculate or change the stored data into new ones and then add again as a new
record.
In this sample code, I know only how to move next.

Thanks,

 

[Michel Walsh]

You can use the Find method of the recordset, or, better yet, specify a
where clause when you open the recordset:


Set lodb=CurrentDb
Set lorst = lodb.OpenRecordset("SELECT * FROM myTable WHERE f1='" & me.txtF1
& "'" )
lorst.MoveFirst
....




and you can do even better replacing all these lines of code with a single
one (and with much less overhead than using a recordset):


DoCmd.RunSQL "UPDATE myTable SET f2=FORMS!FormName!txtF2,
f3=FORMS!FormName!txtF3 WHERE f1=FORMS!FormName!txtF1"





Hoping it may help,
Vanderghast, Access MVP

 

[ooxx]

Hi,

Thanks but this is the one SQL I've got by myself and I am proud of me, but
I don't know you will agree with this :

As I have tested by doing in irk. I have got this Sql. Suppose "C" of F1 is
the only one target I need to insert into Mytable. Then anyway I would use
lordb.movelast and update it later.
In fact the problem is if I would use me.txtF1 to place instead of "C" in
order to have options to choose, it now run error and nothing happened. So
if you could help the code to substitue Me.txtF1 to "C" for me?

Thanks,

This code works in query, but to substitue "C" with me.txtF1 it runs out dry.

INSERT INTO Mytable ( F3, F2, F1 )
SELECT myTable.F3, Last(myTable.F2) AS LastOfF2, Last(myTable.F1) AS LastOfF1
FROM myTable
WHERE (((myTable.F3)=(SELECT MAX(f3)
FROM myTable as X
WHERE (X.F1 = MyTable.F1 ) and X.F1 = "C")))
GROUP BY myTable.F3
HAVING (((1)=1));

 

[Michel Walsh]

INSERT INTO Mytable ( F3, F2, F1 )
SELECT myTable.F3, Last(myTable.F2) AS LastOfF2, Last(myTable.F1) AS
LastOfF1
FROM myTable
WHERE (((myTable.F3)=(SELECT MAX(f3)
FROM myTable as X
WHERE (X.F1 = MyTable.F1 ) and X.F1 = FORMS!FormNameHere!txtF1)))
GROUP BY myTable.F3
HAVING (((1)=1));



and either use the user interface, either use DoCmd.RunSQL "INSERT INTO ...
", but do not use CurrentDb.Execute "INSERT INTO ... " . CurrentDb will not
recognize FORMS!FormName!ControlName.




Vanderghast, Access MVP