How to find the Max Value of Different Fields of a Query? (1 Viewer)

JithuAccess

Member
Local time
Today, 10:02
Joined
Mar 3, 2020
Messages
297
Hello Guys,

This is my query result.

1603807587124.png

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

Enthusiastic Amateur
Local time
Today, 16:02
Joined
Sep 21, 2011
Messages
14,044
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

Enthusiastic Amateur
Local time
Today, 16:02
Joined
Sep 21, 2011
Messages
14,044
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

Member
Local time
Today, 10:02
Joined
Mar 3, 2020
Messages
297
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

Member
Local time
Today, 10:02
Joined
Mar 3, 2020
Messages
297
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

Enthusiastic Amateur
Local time
Today, 16:02
Joined
Sep 21, 2011
Messages
14,044
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

Member
Local time
Today, 10:02
Joined
Mar 3, 2020
Messages
297
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

Super Moderator
Staff member
Local time
Today, 12:02
Joined
Jan 23, 2006
Messages
15,364
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)
Code:
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

I’m here to help
Staff member
Local time
Today, 09:02
Joined
Oct 29, 2018
Messages
21,358
Hi. You could also export your data into Excel and use the MAX() function in Excel.
 

sxschech

Registered User.
Local time
Today, 09:02
Joined
Mar 2, 2010
Messages
791
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])

Code:
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

Member
Local time
Today, 18:02
Joined
May 24, 2020
Messages
35
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
 
Last edited:

JithuAccess

Member
Local time
Today, 10:02
Joined
Mar 3, 2020
Messages
297
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

Member
Local time
Today, 10:02
Joined
Mar 3, 2020
Messages
297
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])

Code:
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

Member
Local time
Today, 10:02
Joined
Mar 3, 2020
Messages
297
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:

1603834172300.png


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

1603834250603.png


Thanks
 

IbrBadri

Member
Local time
Today, 18:02
Joined
May 24, 2020
Messages
35
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

Super Moderator
Staff member
Local time
Today, 12:02
Joined
Feb 19, 2002
Messages
42,970
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.
 

Users who are viewing this thread

Top Bottom