Selecting latest value

andyw27
andyw27 used Ask the Experts™
on
Hi,

Say I have this table of data:

IR 0001      1.0
IR 0002      1.0
IR 0003      1.0
IR 0004      1.0
IR 0002      2.0
IR 0006      1.0
IR 0004      2.0
IR 0008      1.0
IR 0009      1.0
IR 0002      3.0
IR 0011      1.0
IR 0012      1.0
IR 0002      4.0
IR 0014      1.0
IR 0003      2.0
IR 0004      3.0
IR 0004      4.0

How could I create a select statement that would return the row with the latest number? (Second column)  For example if I used “IR 0002” within the where clause I would want it to return 4.0, selecting IR 0003 would return 2.0

The code would also need to work where the 2nd column data is formatted in different ways for example 04-00, 4.0 or 4

Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
try the following;

select [Field1],Max(CInt([Number]))
From Table1
Group by [Field1]
actually, not quite right, bare with me
lets say col1 have IR value nd col2 1.0

then try this

Select col1 , Max(col2) from table_name
where col1='IR 0002'

you cna get for all also
Select col1 , Max(col2) from table_name
group by col1
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
Thanks.

I'm trying to get a value from access into excel.  So far I have this in excel:

Public Function GetADORecordset(ByVal strSql As String) As ADODB.Recordset

    Dim cn As New ADODB.Connection
    Dim rst As ADODB.Recordset
   
    Set cn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = cn
   
    rst.CursorLocation = adUseClient
    rst.CursorType = adOpenStatic
    'rst.LockType = adLockReadOnly
    rst.LockType = adLockOptimistic
    rst.Open strSql
   
    Set rst.ActiveConnection = Nothing
    cn.Close
    Set cn = Nothing
    Set GetADORecordset = rst
   

End Function


Public Function myDLookUpMax(ByVal strField As String, ByVal strDomain As String, _
    Optional strCriteria As Variant)

    Dim rsLk As Recordset
    Dim strSql As String
   
    myDLookUpMax = Null
    If Not IsMissing(strCriteria) Then
        strSql = "SELECT MAX (" & strField & ") FROM " & strDomain & " WHERE " & strCriteria
    Else
        strSql = "SELECT MAX (" & strField & ") FROM " & strDomain
    End If
    MsgBox (strSql)
   
    Set rsLk = GetADORecordset(strSql)
    If Not rsLk.EOF Then myDLookUpMax = rsLk(0) Else myDLookUpMax = "N/A"
    rsLk.Close

End Function

and I call it with this in excel:

=myDLookUpMax("Field6","Docs","Field3 = 0079")

However all I keep getting is #VALUE!

Anything look out of place?

Author

Commented:
Thanks.

I'm trying to get a value from access into excel.  So far I have this in excel:

Public Function GetADORecordset(ByVal strSql As String) As ADODB.Recordset

    Dim cn As New ADODB.Connection
    Dim rst As ADODB.Recordset
   
    Set cn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    rst.ActiveConnection = cn
   
    rst.CursorLocation = adUseClient
    rst.CursorType = adOpenStatic
    'rst.LockType = adLockReadOnly
    rst.LockType = adLockOptimistic
    rst.Open strSql
   
    Set rst.ActiveConnection = Nothing
    cn.Close
    Set cn = Nothing
    Set GetADORecordset = rst
   

End Function


Public Function myDLookUpMax(ByVal strField As String, ByVal strDomain As String, _
    Optional strCriteria As Variant)

    Dim rsLk As Recordset
    Dim strSql As String
   
    myDLookUpMax = Null
    If Not IsMissing(strCriteria) Then
        strSql = "SELECT MAX (" & strField & ") FROM " & strDomain & " WHERE " & strCriteria
    Else
        strSql = "SELECT MAX (" & strField & ") FROM " & strDomain
    End If
    MsgBox (strSql)
   
    Set rsLk = GetADORecordset(strSql)
    If Not rsLk.EOF Then myDLookUpMax = rsLk(0) Else myDLookUpMax = "N/A"
    rsLk.Close

End Function

and I call it with this in excel:

=myDLookUpMax("Field6","Docs","Field3 = 0079")

However all I keep getting is #VALUE!

Anything look out of place?

Commented:
SELECT Table1.Field1, Max(Table1.Field2) AS MaxOfField2
FROM Table1
GROUP BY Table1.Field1;
Commented:
if you are using MAX key word u need to use GROUP BY cluase with that
Top Expert 2010

Commented:
andyw27,

Please define what you mean by "latest number".  If what you mean is, "the last entry for a given value of <column 1>", this will not be possible unless you have a sequential AutoNumber column on the table, or a column that has a date/time stamp for when the entry was made.

Or perhaps do you simply want the maximum value, which is not necessarily the same thing?

Also,

The code would also need to work where the 2nd column data is formatted in different ways for example 04-00, 4.0 or 4

What are ALL of the ways that value can be represented?

Patrick

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial