max no group

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?
0
Utf
10/24/2007 9:01:01 AM
access.queries 6343 articles. 1 followers. Follow

15 Replies
731 Views

Similar Articles

[PageSpeed] 29

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



"ooxx" <ooxx@discussions.microsoft.com> wrote in message 
news:656A681C-6E8A-4CB0-AD8D-A22085C96CD0@microsoft.com...
> 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? 


0
Michel
10/24/2007 10:06:04 AM
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
..

"ooxx" <ooxx@discussions.microsoft.com> wrote in message 
news:656A681C-6E8A-4CB0-AD8D-A22085C96CD0@microsoft.com...
> 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? 


0
John
10/24/2007 12:00:11 PM
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" wrote:

> 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
> ..
> 
> "ooxx" <ooxx@discussions.microsoft.com> wrote in message 
> news:656A681C-6E8A-4CB0-AD8D-A22085C96CD0@microsoft.com...
> > 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? 
> 
> 
> 
0
Utf
10/25/2007 6:36:00 AM
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
'====================================================


ooxx wrote:
> 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" wrote:
> 
>> 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
>> ..
>>
>> "ooxx" <ooxx@discussions.microsoft.com> wrote in message 
>> news:656A681C-6E8A-4CB0-AD8D-A22085C96CD0@microsoft.com...
>>> 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? 
>>
>>
0
John
10/25/2007 1:00:23 PM
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



"ooxx" <ooxx@discussions.microsoft.com> wrote in message 
news:3B636D3B-7357-4FD4-9AC5-51AB13B46F3B@microsoft.com...
> 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" wrote:
>
>> 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
>> ..
>>
>> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
>> news:656A681C-6E8A-4CB0-AD8D-A22085C96CD0@microsoft.com...
>> > 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?
>>
>>
>> 


0
Michel
10/25/2007 1:16:08 PM
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?


"Michel Walsh" wrote:

> 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
> 
> 
> 
> "ooxx" <ooxx@discussions.microsoft.com> wrote in message 
> news:3B636D3B-7357-4FD4-9AC5-51AB13B46F3B@microsoft.com...
> > 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" wrote:
> >
> >> 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
> >> ..
> >>
> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
> >> news:656A681C-6E8A-4CB0-AD8D-A22085C96CD0@microsoft.com...
> >> > 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?
> >>
> >>
> >> 
> 
> 
> 
0
Utf
10/26/2007 3:10:01 AM
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
..

"ooxx" <ooxx@discussions.microsoft.com> wrote in message 
news:F30630D7-15EC-4A1F-9E6B-2C87B1A58FF0@microsoft.com...
> 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?
>
>
> "Michel Walsh" wrote:
>
>> 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
>>
>>
>>
>> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
>> news:3B636D3B-7357-4FD4-9AC5-51AB13B46F3B@microsoft.com...
>> > 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" wrote:
>> >
>> >> 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
>> >> ..
>> >>
>> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
>> >> news:656A681C-6E8A-4CB0-AD8D-A22085C96CD0@microsoft.com...
>> >> > 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?
>> >>
>> >>
>> >>
>>
>>
>> 


0
John
10/26/2007 11:35:52 AM
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


"John Spencer" wrote:

> 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
> ..
> 
> "ooxx" <ooxx@discussions.microsoft.com> wrote in message 
> news:F30630D7-15EC-4A1F-9E6B-2C87B1A58FF0@microsoft.com...
> > 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?
> >
> >
> > "Michel Walsh" wrote:
> >
> >> 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
> >>
> >>
> >>
> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
> >> news:3B636D3B-7357-4FD4-9AC5-51AB13B46F3B@microsoft.com...
> >> > 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" wrote:
> >> >
> >> >> 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
> >> >> ..
> >> >>
> >> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
> >> >> news:656A681C-6E8A-4CB0-AD8D-A22085C96CD0@microsoft.com...
> >> >> > 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?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
Utf
11/20/2007 4:51:00 AM
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


"ooxx" <ooxx@discussions.microsoft.com> wrote in message 
news:4FB336D2-4FE5-4BEB-8B48-4329F3FCA1C0@microsoft.com...
> 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
>
>
> "John Spencer" wrote:
>
>> 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
>> ..
>>
>> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
>> news:F30630D7-15EC-4A1F-9E6B-2C87B1A58FF0@microsoft.com...
>> > 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?
>> >
>> >
>> > "Michel Walsh" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >>
>> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
>> >> news:3B636D3B-7357-4FD4-9AC5-51AB13B46F3B@microsoft.com...
>> >> > 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" wrote:
>> >> >
>> >> >> 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
>> >> >> ..
>> >> >>
>> >> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
>> >> >> news:656A681C-6E8A-4CB0-AD8D-A22085C96CD0@microsoft.com...
>> >> >> > 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?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>> 


0
Michel
11/20/2007 2:17:33 PM
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" wrote:

> 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
> 
> 
> "ooxx" <ooxx@discussions.microsoft.com> wrote in message 
> news:4FB336D2-4FE5-4BEB-8B48-4329F3FCA1C0@microsoft.com...
> > 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
> >
> >
> > "John Spencer" wrote:
> >
> >> 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
> >> ..
> >>
> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
> >> news:F30630D7-15EC-4A1F-9E6B-2C87B1A58FF0@microsoft.com...
> >> > 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?
> >> >
> >> >
> >> > "Michel Walsh" wrote:
> >> >
> >> >> 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
> >> >>
> >> >>
> >> >>
> >> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
> >> >> news:3B636D3B-7357-4FD4-9AC5-51AB13B46F3B@microsoft.com...
> >> >> > 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" wrote:
> >> >> >
> >> >> >> 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
> >> >> >> ..
> >> >> >>
> >> >> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
> >> >> >> news:656A681C-6E8A-4CB0-AD8D-A22085C96CD0@microsoft.com...
> >> >> >> > 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?
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
Utf
11/21/2007 7:02:01 AM
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


"ooxx" <ooxx@discussions.microsoft.com> wrote in message 
news:CCF59789-1FF0-4571-9BB4-8D5B1CE2E9E3@microsoft.com...
> 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" wrote:
>
>> 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
>>
>>
>> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
>> news:4FB336D2-4FE5-4BEB-8B48-4329F3FCA1C0@microsoft.com...
>> > 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
>> >
>> >
>> > "John Spencer" wrote:
>> >
>> >> 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
>> >> ..
>> >>
>> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
>> >> news:F30630D7-15EC-4A1F-9E6B-2C87B1A58FF0@microsoft.com...
>> >> > 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?
>> >> >
>> >> >
>> >> > "Michel Walsh" wrote:
>> >> >
>> >> >> 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
>> >> >>
>> >> >>
>> >> >>
>> >> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
>> >> >> news:3B636D3B-7357-4FD4-9AC5-51AB13B46F3B@microsoft.com...
>> >> >> > 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" wrote:
>> >> >> >
>> >> >> >> 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
>> >> >> >> ..
>> >> >> >>
>> >> >> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
>> >> >> >> news:656A681C-6E8A-4CB0-AD8D-A22085C96CD0@microsoft.com...
>> >> >> >> > 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?
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>> 


0
Michel
11/21/2007 2:24:01 PM
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" wrote:

> 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
> 
> 
> "ooxx" <ooxx@discussions.microsoft.com> wrote in message 
> news:CCF59789-1FF0-4571-9BB4-8D5B1CE2E9E3@microsoft.com...
> > 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" wrote:
> >
> >> 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
> >>
> >>
> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
> >> news:4FB336D2-4FE5-4BEB-8B48-4329F3FCA1C0@microsoft.com...
> >> > 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
> >> >
> >> >
> >> > "John Spencer" wrote:
> >> >
> >> >> 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
> >> >> ..
> >> >>
> >> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
> >> >> news:F30630D7-15EC-4A1F-9E6B-2C87B1A58FF0@microsoft.com...
> >> >> > 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?
> >> >> >
> >> >> >
> >> >> > "Michel Walsh" wrote:
> >> >> >
> >> >> >> 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
> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
> >> >> >> news:3B636D3B-7357-4FD4-9AC5-51AB13B46F3B@microsoft.com...
> >> >> >> > 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" wrote:
> >> >> >> >
> >> >> >> >> 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
> >> >> >> >> ..
> >> >> >> >>
> >> >> >> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
> >> >> >> >> news:656A681C-6E8A-4CB0-AD8D-A22085C96CD0@microsoft.com...
> >> >> >> >> > 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?
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
Utf
11/22/2007 3:59:00 AM
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" <ooxx@discussions.microsoft.com> wrote in message 
news:AF3C2C46-B0BD-4750-AD5C-6899D7676F8A@microsoft.com...
> 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" wrote:
>
>> 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
>>
>>
>> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
>> news:CCF59789-1FF0-4571-9BB4-8D5B1CE2E9E3@microsoft.com...
>> > 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" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
>> >> news:4FB336D2-4FE5-4BEB-8B48-4329F3FCA1C0@microsoft.com...
>> >> > 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
>> >> >
>> >> >
>> >> > "John Spencer" wrote:
>> >> >
>> >> >> 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
>> >> >> ..
>> >> >>
>> >> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
>> >> >> news:F30630D7-15EC-4A1F-9E6B-2C87B1A58FF0@microsoft.com...
>> >> >> > 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?
>> >> >> >
>> >> >> >
>> >> >> > "Michel Walsh" wrote:
>> >> >> >
>> >> >> >> 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
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
>> >> >> >> news:3B636D3B-7357-4FD4-9AC5-51AB13B46F3B@microsoft.com...
>> >> >> >> > 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" wrote:
>> >> >> >> >
>> >> >> >> >> 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
>> >> >> >> >> ..
>> >> >> >> >>
>> >> >> >> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
>> >> >> >> >> news:656A681C-6E8A-4CB0-AD8D-A22085C96CD0@microsoft.com...
>> >> >> >> >> > 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?
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>> 


0
Michel
11/22/2007 1:22:07 PM
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" wrote:

> 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" <ooxx@discussions.microsoft.com> wrote in message 
> news:AF3C2C46-B0BD-4750-AD5C-6899D7676F8A@microsoft.com...
> > 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" wrote:
> >
> >> 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
> >>
> >>
> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
> >> news:CCF59789-1FF0-4571-9BB4-8D5B1CE2E9E3@microsoft.com...
> >> > 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" wrote:
> >> >
> >> >> 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
> >> >>
> >> >>
> >> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
> >> >> news:4FB336D2-4FE5-4BEB-8B48-4329F3FCA1C0@microsoft.com...
> >> >> > 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
> >> >> >
> >> >> >
> >> >> > "John Spencer" wrote:
> >> >> >
> >> >> >> 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
> >> >> >> ..
> >> >> >>
> >> >> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
> >> >> >> news:F30630D7-15EC-4A1F-9E6B-2C87B1A58FF0@microsoft.com...
> >> >> >> > 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?
> >> >> >> >
> >> >> >> >
> >> >> >> > "Michel Walsh" wrote:
> >> >> >> >
> >> >> >> >> 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
> >> >> >> >>
> >> >> >> >>
> >> >> >> >>
> >> >> >> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
> >> >> >> >> news:3B636D3B-7357-4FD4-9AC5-51AB13B46F3B@microsoft.com...
> >> >> >> >> > 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" wrote:
> >> >> >> >> >
> >> >> >> >> >> 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
0
Utf
11/23/2007 7:40:01 AM

 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



"ooxx" <ooxx@discussions.microsoft.com> wrote in message 
news:DEEE3D41-F4AD-4C2B-A208-CC027BE34EB7@microsoft.com...
> 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" wrote:
>
>> 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" <ooxx@discussions.microsoft.com> wrote in message
>> news:AF3C2C46-B0BD-4750-AD5C-6899D7676F8A@microsoft.com...
>> > 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" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
>> >> news:CCF59789-1FF0-4571-9BB4-8D5B1CE2E9E3@microsoft.com...
>> >> > 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" wrote:
>> >> >
>> >> >> 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
>> >> >>
>> >> >>
>> >> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
>> >> >> news:4FB336D2-4FE5-4BEB-8B48-4329F3FCA1C0@microsoft.com...
>> >> >> > 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
>> >> >> >
>> >> >> >
>> >> >> > "John Spencer" wrote:
>> >> >> >
>> >> >> >> 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
>> >> >> >> ..
>> >> >> >>
>> >> >> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
>> >> >> >> news:F30630D7-15EC-4A1F-9E6B-2C87B1A58FF0@microsoft.com...
>> >> >> >> > 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?
>> >> >> >> >
>> >> >> >> >
>> >> >> >> > "Michel Walsh" wrote:
>> >> >> >> >
>> >> >> >> >> 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
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >>
>> >> >> >> >> "ooxx" <ooxx@discussions.microsoft.com> wrote in message
>> >> >> >> >> news:3B636D3B-7357-4FD4-9AC5-51AB13B46F3B@microsoft.com...
>> >> >> >> >> > 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" wrote:
>> >> >> >> >> >
>> >> >> >> >> >> 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 


0
Michel
11/23/2007 12:17:35 PM
Reply:

Similar Artilces:

Sum of each grouped item in a report
I have a form that logs time spent on auditing activities for the Internal Auditors in my company. Each time the auditor performs an audit related task, they enter a new record under their name. This means they could have many occurences of the same function. I then created a query/report that breaks down the different auditing activities for each internal auditor. Management has now asked for a report that gives a total for each activity for each internal auditor (example - John Smith: Audit Prep - 2.25, Follow-up - 3.75, Audit - 7.50). I want to figure out how to do this on a rep...

concatenate while grouping
Thanks for any help. I am trying to concatenate a field from record(s) into a field, while grouping them. For example, the query (with no concatenation, but with grouping) is like: SELECT Table!name, Table!date FROM Table GROUP BY Table!name, Table!date I would like to make a query that along with grouping by name and date, makes another field, that is a concatenation of all fields called Policy. I am trying to do this with Duane Hookum's concatenation function, but can't figure it out, I think because his function doesn't need a grouping to do it. But I have to use a groupi...

Grouping in a Pivot Table
I have created a Pivot Table off of a spreadsheet containing sales related information. I am trying to group the "Due Date" field into months and I keep receiving an error that I cannot group the selection. I have used this feature before with success and I am stumped as to why it is not working this time. Help! Most often it has to do with having blank cells or text mixed in with your dates. Check out this link. Once you have changed all of your items into dates you may have to refresh your pivot table twice before you will be able to group by dates. http://www...

Why are all my Excel docs in Google Groups opening as Read Only?
It looks like the Excel docs are all Excel 97-2003 files. I'm on Vista. Is this a compatability issue? The Google Group admin for these docs says it is not an issue with the security access level. ...

Can Entourage be used to access Google Groups
I am in the process of switching from PC to Mac. In Entourage, I see this news server. Can I get some of my newsgroups in Google Groups to show on Entourage or Mac Mail? Responses off group will be welcome if I have asked something everyone except me knows. Thanks, Michael Plog On 1/31/10 3:25 PM, Michael Plog wrote: > I am in the process of switching from PC to Mac. In Entourage, I see this > news server. Can I get some of my newsgroups in Google Groups to show on > Entourage or Mac Mail? > > Responses off group will be welcome if I have asked something e...

Group Worksheets and Modify Chart Formats?
Searched this group and other Excel groups with no success. I have 50 plus worksheet, and each worksheet has two charts (multiple stacked bar charts). We've finally finished entering the data into each worksheet, and I need to "spruce up" the charts. I didn't worry about this until now, as I thought (perhaps incorrectly) that I could just group the worksheets, and change the formatting as required, and each chart on each worksheet would be updated. Well, there doesn't appear to be any way to do this, as I cannot select the chart with more than one worksheet selected. ...

Move server between Admin Groups #2
We are going to be going through a reorganization soon and will need to move some exchange servers from one admin group to another. I know there is not a native way in Exchange 2003 to do this. Does anyone have ideas on how to accomplish this besides a swing server to the new Admin Group? Third party Software? Our environment is 2003 Forest functional and domain level. Exchange 2003 mixed mode. On Tue, 3 Jan 2006 14:16:23 -0500, "Tim Kalligonis" <tkalligonis@comcast.net> wrote: >We are going to be going through a reorganization soon and will need to move >so...

Win 7 can't join SBS 2008
I've read the blogs and edits for the GPO settings, but I can't believe the MS is going to force us to fix this issue by editing a GPO instead of publishing a update for this. Do I really have to edit the GPO or is there an update I'm missing that will allow http://connect to work with Windows 7 for SBS 2008 server? -- You can't trust your best friends, your five senses, only the little voice inside you that most civilians don't even hear -- Listen to that. Trust yourself. spam999free@rrohio.com (remove 999 for proper email address) An update has bee...

Why aren't dates always available to group in pivot tables?
I download data that gives me sales number by individual day. I build a pivot table to get total sales by day and then group the process day by month or quarter. Sometimes this works perfectly, while other times I get the message 'Cannot group that selection'. Has this happened to anyone else? Is there a limit to the number of rows or columns that can be grouped? You'll see that error message if the date field contains a blank cell, or text entry. There's a bit more information, and some workarounds, here: http://www.contextures.com/xlPivot07.html#Problems JLM wr...

Group lisiting in Contacts
I am curious as to why some members in a Group in Contacts have " (e-mail) " next to their name and others do not. All the 'members' I've added have e-mail addresses. This is no big deal, I'm just curious. Thanks, DH Dave Horne <davehorne@home.nl> wrote: > I am curious as to why some members in a Group in Contacts have " > (e-mail) " next to their name and others do not. All the 'members' > I've added have e-mail addresses. This is no big deal, I'm just > curious. Thanks, DH What is your definition of a "group...

Pb with a distribution group to send mails to contacts
Hi, I use Exchange 2k3, I want to create a distribution group to send mails to people inside the compagny (with exchange mailbox) and people with a external mailbox. for the external mailbox, I create a contact, I send mail to the contact, it's ok. if the contact is member of the distribution group, when I send mail to the distribution group the mail isn't transmited to the contact. I receive this mail from the server : ******************************************************************************* Certains des destinataires ou tous les destinataires n'ont pas re´┐Żu votre me...

Grouping daily records by week for chart display
I have a table of daily sales transactions, but need to chart the data by week (and month). I will be showing the sum of sales for a given week as a single data point. Is there a simple way to do this in creating the chart, or do I first need to pivot the data and then create the chart? Hi Mike, You can use the Pivot Table tool to do this or you can do it in the spreadsheet with a regular chart. You also have the option to use the pivot table as the source for a non-pivot table chart. The real question is which method do you want? I will illustrate one of the solutions, if you want on...

Outlook Group mailbox
I am trying to connect and read a group mailbox inbox to process....the issue I get is I can connect to my inbox with no problem...but I don't know the syntax and properties to do this with a group mailbox..can someone shed some lighton this. thanks Submitted via EggHeadCafe - Software Developer Portal of Choice Perfectionist's PowerPoint Tip: E-mail Your PowerPoint To Others http://www.eggheadcafe.com/tutorials/aspnet/899ee174-516f-4e3a-851f-cd74468df792/perfectionists-powerpoin.aspx See the GetSharedDefaultFolder function. For more questions related to programming pleas...

Unable to send from a group
Have a mail enabled distribution group. A security group has send as permissions for this group, but users belonging to the security group receive the error below when trying to send from the dist. group. Tried assigning permsisions for the individual users, same error. You do not have permission to send to this recipient. For assistance, contact your system administrator. MSEXCH:MSExchangeIS:/DC=com/DC=domain/DC=dm:MAIL Hi, You can try giving the security group full mailbox access from the ADUC snapin. Regards Martin "Mark" <Mark@discussions.microsoft.com&...

script to count the number of mailboxes on all storage groups on a single mail server
Gurus, Is there a script I can run which can count the number of mailboxes on all storage groups on a single mail server? "Spin" wrote: > Is there a script I can run which can count the number of mailboxes on all > storage groups on a single mail server? Try http://www.scriptinganswers.com; they have several (for free). ...

Open an e-mailed group on another computer
How would I e-mail a named Group address list of 30 names on my PC to another PC and how would that person put the Group in their Outlook Contacts? Thanks, Dan <Dan@discussions.microsoft.com> wrote: > How would I e-mail a named Group address list of 30 names on my PC to > another PC and how would that person put the Group in their Outlook > Contacts? Thanks, Are you talking about a Distribution List? Create a Rich Text message and drag your DL to the open message's body. It will attach there. Then send it. -- Brian Tillman Yes, Brian. I tried a lot of different ...

Tracking Distribution Group Usage (Exchange 2003)
Is there some way, within Exchange 2003, to determine what distribution groups are being used, how often they are being used, and by whom? I'd like to remove any groups that aren't being used but I don't know how to determine which ones they would be. -Alan ...

Sorting dates within groups.
I have a RptStatement to send to customers each month. I have grouped the data by month on the InvDate field. But the dates (InvDate) within a group are in descending order, even though the setting in the "Grouping and Sorting" box has them in ascending order. Please help me how to fix this. Regards, Frank. Add InvDate to the Sorting And Grouping dialog a 2nd time. The first instance tells Access to sort the months grouping ascending. The 2nd instance tells Access to sort the dates within the grouping. -- Allen Browne - Microsoft MVP. Perth, Western Austral...

send on behalf on Groups
Hi, I have a customer asking is there any way to send an email on behalf of a group, instead of send as. I konw with a user you can do that but with a group I can't find it? Is there any way? Thanks "eduardo" <eduardo@discussions.microsoft.com> wrote in message news:A96BBDDE-B69C-4164-84E0-3A344531DCAB@microsoft.com... > Hi, I have a customer asking is there any way to send an email on behalf > of a > group, instead of send as. > I konw with a user you can do that but with a group I can't find it? > Is there any way? You can send as a mail-enabled *...

groups detail section totals access 2003
Hi all, I know this can be done, but haven't figured out how yet. I have what basically is a summary report that my sql comes up with for the detail rows. I want to total these rows in the report and display immediately below the detail section. I don't really want to group anything, but want to treat the whole detail section as a group. That being said, how can I get a "group footer" on the designer so I can add my total columns. If I use "sorting and grouping", it starts grouping things and that is not what I want. I don't want to use the "page foo...

avg, min and max
Excel is silly: If cell range A1:A10 is empty, =average(a1:a10) will result in a #DIV/0! error message, =max(a1:a10) will result in 0, and =min(a1:a10) will also result in 0. I work with large intrinsic data sets, but sometimes have to use these 3 functions on them in macros using VBA. I need to avoid the #DIV/0! errors that occur in the averaging column, and do this by using the formula =IF(ISERROR(AVERAGE(a1:a10)), "", AVERAGE(a1:a10)). By this I replace the error messages with blanks. So here's my question: When I'm using the MIN or MAX functions, is there a co...

Domian Local into Domain Admins Group
How do I make a 'Domain Local' security group which contains a Universal group from another domain, a member of the Global 'Domain Admins' group? DL's can't become a member of GG's Cosmo, you can not. Domain global groups can contain only users and global groups from the same domain... If you need to grant Domain Admins equivalent privileges to accounts from other domains, add them to the domain local Administrators group and local Administrators groups on all domain member computers... hth Marcin "Cosmo" <Cosmo@discussions.microso...

How to install Exchange in an "Administrative Group"
I am running Windows 2003 and Exchange 2003 on a DC in my lab. Exchange is installed in the "First Administrative Group". Now I want to install a second Exchange 2003 Server in another "Administrative Group" called "Midwest". This is what I did: 1. Installed the second Exchange 2003 server in the "First Administrative Group". 2. Created the second "Administrative Group" called "Midwest". 3. Tried to move the second Exchange 2003 server into the "Midwest" Administrative Group. The problem was that I was not able to &q...

How to create an "and" rule in Query Based Distribution Groups
Hi, With Exchange 2003 Query Based Distribution groups, is it possible to create an "and" rule? ie, all users who are based in "London" "and" have the first name "John"? Thanks, Curtis. -- Please reply to news group only. Thank you. Sure. (&(attribute1=blah)(attribute2=blah)) http://msdn.microsoft.com/library/en-us/adsi/adsi/search_filter_syntax.asp?frame=true -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------- "Curtis Fray" <xxx@xxx.com> wrote in message news:OjVc...

Auto Forward mail to a group of External addresses
Can someone please tell me how I create an Auto Forward to a group of external email addresses. I want to setup an auto forward on my exchange so that all mail sent to forward@mydomain.com is then forwarded to a group of email accounts such as me@hotmail.com, me@yahoo.com, user@mail.com. Can someone please tell me how I set this up on an exchange server. I know how to do it for an individual but I can't work out how I create an auto forward for a group of people. Thanks for any advice! what version Exchange? only the terminology is different...create custom recipients/contacts ...