How to find the Max Value of Different Fields of a Query?

[JithuAccess]

Hello Guys,

This is my query result.

I have 5 Fields (All are Date Fields) More Information 1 to More Information 5. Is there any way find the date of the latest correspondence sent? In this query, I want to show "2015-07-10" in the query field "Last Correspondence Sent" and if the value for the field More Information 5 is "2020-10-27" I want to display that value.

Just wondering if there is any method in Access to get this query result.

Thanks

 

[Gasman]

Wouldn't it just be the last field.?
You are in this predicament as your structure is wrong. They should be separate records then it would be a simple Max() function.

What happens when filed 5 is completed, where does the next date go?

 

[Gasman]

I would write a function and put the logic in that for such a bad structure. Pass in all the fields and return the required value.?

 

[JithuAccess]

Wouldn't it just be the last field.?
You are in this predicament as your structure is wrong. They should be separate records then it would be a simple Max() function.

What happens when filed 5 is completed, where does the next date go?

Thanks a lot for your reply. There should be only 5 "More Information" maximum. The problem is we have more than 5,000 records and some of the records have the values for the field "More Information" up to 5 and some of the Records just 1. And what would be the easy and simple method to get this done?

Thanks

 

[JithuAccess]

What about create a simple Make Table Query and store all the More Information 1 to More Information 5 in a Single field, then get the Max value of the date field?

 

[Gasman]

What about create a simple Make Table Query and store all the More Information 1 to More Information 5 in a Single field, then get the Max value of the date field?

When you are moving 5 fields into one, you may as well just use a function I would have thought?
Unless you are going to do it once only to get the correct structure?, but that would involve changing your process.

 

[JithuAccess]

When you are moving 5 fields into one, you may as well just use a function I would have thought?
Unless you are going to do it once only to get the correct structure?, but that would involve changing your process.

Thanks a lot for your suggestions Sir

 

[jdraw]

As gasman has indicated, your structure is incorrect for database. You might consider a table along these lines - where there is a table to identify interactions with a client(or subject or whatever this letter and more info requests is about)

transId               unique identifier
transrefClient    specific subject/client
transDate          date of request/email/letter
....

So there would be 0......X records for the more info requests, and you could sequence these by transDate as necessary.

 

[theDBguy]

Hi. You could also export your data into Excel and use the MAX() function in Excel.

 

[sxschech]

This function from Allen Browne may work for your situation. The included link has an explanation. Put the code in a standard module.

To use in your query for the column Last Correspondence Sent:

LastCorrespondenceSent:MaxofList([More Information1], [More Information2], [More Information3], [More Information4], [More Information5])

Function MaxOfList(ParamArray varValues()) As Variant
'http://allenbrowne.com/func-09.html
Dim i As Integer        'Loop controller.
    Dim varMax As Variant   'Largest value found so far.

    varMax = Null           'Initialize to null

    For i = LBound(varValues) To UBound(varValues)
        If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then
            If varMax >= varValues(i) Then
                'do nothing
            Else
                varMax = varValues(i)
            End If
        End If
    Next

    MaxOfList = varMax
End Function

 

[IbrBadri]

Hello Guys,

This is my query result.

View attachment 86149
I have 5 Fields (All are Date Fields) More Information 1 to More Information 5. Is there any way find the date of the latest correspondence sent? In this query, I want to show "2015-07-10" in the query field "Last Correspondence Sent" and if the value for the field More Information 5 is "2020-10-27" I want to display that value.

Just wondering if there is any method in Access to get this query result.

Thanks

Hi, I thought you could make a UNION query, unified with tow column, Id and Information, For the five fields

Like this:
SELECT ID, More Information1 AS Information
FROM TableName UNION
SELECT ID, More Information2 AS Information
FROM TableName UNION
SELECT ID, More Information3 AS Information
FROM TableName UNION
SELECT ID, More Information4 AS Information
FROM TableName UNION
SELECT ID, More Information5 AS Information
FROM TableName
ORDER BY Information DESC;

TableName = Your table name have the information
or just one field "Information" if you don't care about the ID as reference

 

[JithuAccess]

Hi, I thought you could make a UNION query, unified with tow column, Id and Information, For the five fields

Like this:
SELECT ID, More Information1 AS Information
FROM TableName UNION
SELECT ID, More Information2 AS Information
FROM TableName UNION
SELECT ID, More Information3 AS Information
FROM TableName UNION
SELECT ID, More Information4 AS Information
FROM TableName UNION
SELECT ID, More Information5 AS Information
FROM TableName
ORDER BY Information DESC;

TableName = Your table name have the information
or just one field "Information" if you don't care about the ID as reference

It works. Thanks a lot for your kind help. This was the simplest and easy Method. Thanks a lot Once again

 

[JithuAccess]

This function from Allen Browne may work for your situation. The included link has an explanation. Put the code in a standard module.

To use in your query for the column Last Correspondence Sent:

LastCorrespondenceSent:MaxofList([More Information1], [More Information2], [More Information3], [More Information4], [More Information5])

Function MaxOfList(ParamArray varValues()) As Variant
'http://allenbrowne.com/func-09.html
Dim i As Integer        'Loop controller.
    Dim varMax As Variant   'Largest value found so far.

    varMax = Null           'Initialize to null

    For i = LBound(varValues) To UBound(varValues)
        If IsNumeric(varValues(i)) Or IsDate(varValues(i)) Then
            If varMax >= varValues(i) Then
                'do nothing
            Else
                varMax = varValues(i)
            End If
        End If
    Next

    MaxOfList = varMax
End Function

Thanks a lot for your time and effort

 

[JithuAccess]

Hi. You could also export your data into Excel and use the MAX() function in Excel.

Thanks a lot. As advised by the Forum member, I used Union Query and it works perfect. Thanks a lot

 

[theDBguy]

Thanks a lot. As advised by the Forum member, I used Union Query and it works perfect. Thanks a lot

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[JithuAccess]

Hi. Glad to hear you got it sorted out. Good luck with your project.

Thanks a lot Sir

 

[IbrBadri]

You are welcome.

Happy to help.

 

[JithuAccess]

You are welcome.

Happy to help.

I am going to disturb you again. So this query is working fine. Just wondering whether we can use a Max Function in Union Query. This is my Query Result:

Here, can we just display the Max value of this particular ID like this:

Thanks

 

[IbrBadri]

I am going to disturb you again. So this query is working fine. Just wondering whether we can use a Max Function in Union Query. This is my Query Result:

View attachment 86164

Here, can we just display the Max value of this particular ID like this:

View attachment 86165

Thanks

just you can make a new simple query reading from this union query, in the field write :-
MaxDAte: Max(More Info Date)
It will show only the max date

 

[Pat Hartman]

Create a new query and use the Union Query as the source rather than a table.

There should be only 5 "More Information" maximum

So what. When you have more than one of something, you have many. It is always better to do things correctly rather than make excuses to take "shortcuts" since "shortcuts" are rarely short and never expandable.