Solved

Access Highest Date Query

Posted on 2009-03-29
11
869 Views
Last Modified: 2013-11-27
Hi,
Im probably being pretty dumb here but I wonder if somebody can answer me this quickly.

Given a table with a number of date fields in it i.e.
DATE1 DATE2 DATE3 DATE4

They may or may not have any data entered for some or all fields and can also be NULL how would I run a query to loop through each record and display the value of the highest date present in a Fifth field like this.

DATE1 DATE2 DATE3 DATE4 HIGHESTDATE

I tried playing around with MAX etc but cant seem to get anywhere and running out of time.
0
Comment
Question by:ddglas
  • 4
  • 2
  • 2
  • +2
11 Comments
 
LVL 4

Expert Comment

by:dublingills
Comment Utility
You can't compare using max as max is used on the same field across different records.  You need to compare the value of each field using one of:

1.  Iif - you'll need to nest these.
or
2. If you are ok with writing VBA you can use a VBA function in a module, something like:

function MaxDate(date1 as date, date2 as date, date3 as date, date4 as date)
dim result as date
if date1 > date2 then result = date1 else result = date2
if date3 > result then result = date3
if date4 > result then result = date4
maxdate = result
end function

The VBA method is by far the easiest.

HTH
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
something like this:

SELECT Table1.DATE1 , Table1.DATE2 , Table1.DATE3 ,   Table1.DATE2,  (SELECT Max([HIGHESTDATE]) AS MaxDate FROM Table1;) AS Expr1
FROM Table1;
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 total points
Comment Utility
Hello ddglas,

You could suss this out using Switch() or nested IIf(), but like dublingills I would be inclined to use a UDF
to give the appearance of a cleaner expression.

The UDF below is highly flexible, allowing you to calculate a min, max, or avg, for any number of inputs
(up to the limit for a ParamArray, which I think is 31).  You can also pass text, dates, or numbers as
the arguments.  (If you pass dates or non-numbers when the type is "avg", they get ignored in computing
the average.)

Use the function like this:

SELECT Date1, Date2, Date3, Date4, RowStats("Max", Date1, Date2, Date3, Date4) AS MaxDate
FROM SomeTable

Regards,

Patrick
Function RowStats(Stat As String, ParamArray Vars())
    
    Dim Numerator As Double
    Dim Denominator As Double
    Dim Counter As Long
    Dim Result
    
    Select Case UCase(Stat)
        Case "MIN"
            Result = Vars(LBound(Vars))
            For Counter = LBound(Vars) + 1 To UBound(Vars)
                If Vars(Counter) < Result Then Result = Vars(Counter)
            Next
        Case "MAX"
            Result = Vars(LBound(Vars))
            For Counter = LBound(Vars) + 1 To UBound(Vars)
                If Vars(Counter) > Result Then Result = Vars(Counter)
            Next
        Case "AVG"
            For Counter = LBound(Vars) To UBound(Vars)
                If IsNumeric(Vars(Counter)) Then
                    Numerator = Numerator + Vars(Counter)
                    Denominator = Denominator + 1
                End If
            Next
            Result = Numerator / Denominator
    End Select
    
    RowStats = Result
    
End Function

Open in new window

0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
The problem you have is that this is not a normalized table, so you simply won't be able to use MAX() (each of those date fields should be in a seperate record in a related table).
There are a couple of approaches.  You can use a bunch of IIF's in the query itself (difficult to debug), call a custom function passing all the dates and returning the highest, or do this in code.  I think what you be best is a custom function.  Paste the following into a general module:

Function GetHighestDate(varDate1 As Variant, varDate2 As Variant, varDate3 As Variant, varDate4 As Variant) As Variant
  If IsNull(varDate1) And IsNull(varDate2) And IsNull(varDate3) And IsNull(varDate4) Then
    GetHighestDate = Null
  Else
    GetHighestDate = Nz(varDate1, 0)
    If Nz(varDate2, 0) > GetHighestDate Then GetHighestDate = varDate2
    If Nz(varDate3, 0) > GetHighestDate Then GetHighestDate = varDate3
    If Nz(varDate4, 0) > GetHighestDate Then GetHighestDate = varDate4
  End If
End Function
  In your query, define a column as HighestDate:GetHighestDate([<datefieldname1>],[<datefieldname2>],[<datefieldname3>],[<datefieldname4>]
  replacing (and including) everything with <> with the actual names of your date fields.
JimD.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
I'm reading this different:

"and display the value of the highest date present in a Fifth field"
                                                                                 ^^^^^^^^^^^^^

mx
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Patrick's deal is slick...I like it<g>.  Got this one bookmarked.  Best generic approach to handling a non-normalized table problem like this that I've seen yet.
JimD.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
Jim,

Thanks for the compliment!  BTW, here is an expanded version, which also handles standard deviation
(sample and population) and variance (sample and population).

Regards,

Patrick
Function RowStats(Stat As String, ParamArray Vars())
    
    Dim Numerator As Double
    Dim Denominator As Double
    Dim Counter As Long
    Dim Result
    
    Stat = UCase(Stat)
    
    Select Case Stat
        Case "MIN"
            Result = Vars(LBound(Vars))
            For Counter = LBound(Vars) + 1 To UBound(Vars)
                If Vars(Counter) < Result Then Result = Vars(Counter)
            Next
        Case "MAX"
            Result = Vars(LBound(Vars))
            For Counter = LBound(Vars) + 1 To UBound(Vars)
                If Vars(Counter) > Result Then Result = Vars(Counter)
            Next
        Case "AVG"
            For Counter = LBound(Vars) To UBound(Vars)
                If IsNumeric(Vars(Counter)) Then
                    Numerator = Numerator + Vars(Counter)
                    Denominator = Denominator + 1
                End If
            Next
            Result = Numerator / Denominator
        Case "STDEV", "STDEVP", "VAR", "VARP"
            For Counter = LBound(Vars) To UBound(Vars)
                If IsNumeric(Vars(Counter)) Then
                    Numerator = Numerator + Vars(Counter)
                    Denominator = Denominator + 1
                End If
            Next
            For Counter = LBound(Vars) To UBound(Vars)
                If IsNumeric(Vars(Counter)) Then Result = Result + (Vars(Counter) - (Numerator / Denominator)) ^ 2
            Next
            Result = Result / IIf(Stat Like "*P", Denominator, Denominator - 1)
            If Stat Like "S*" Then Result = Result ^ 0.5
    End Select
    
    RowStats = Result
    
End Function

Open in new window

0
 
LVL 1

Author Comment

by:ddglas
Comment Utility
Im getting undefined function "Rowstats" when trying to run that, any idea what i've done wrong.

I have copied the function in.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
ddglas,

Make sure that you placed the UDF in a regular module, and not a form/report/class module.  Also make
sure that that module is not using an Option Private Module declaration.  Do a debug/compile from the
VBA Editor, and check your macro security setting.

Regards,

Patrick
0
 
LVL 1

Author Closing Comment

by:ddglas
Comment Utility
Thanks a Million, very useful
0
 
LVL 92

Expert Comment

by:Patrick Matthews
Comment Utility
ddglas,

Glad to help.  BTW, here is a final version.  The version I posted at first has a flaw in the min and max calculations:
if the first element in the array is null, the result comes back null.  The version below fixes that flaw, and is also
documented.

Regards,

Patrick
Function RowStats(Stat As String, ParamArray Vars())
    
    ' Function by Patrick Matthews
    
    ' This Access UDF calculates various stats for the values passed into the ParamArray.  It
    ' was originally designed for passing several values from a particular row set for
    ' evaluation; since the values come from the same row, the usual aggregate functions would
    ' not be appropriate.
    
    ' The Stat variable (NOT case sensitive) determines what statistic is calculated:
    ' "min", "max":     minimum or maximum
    ' "sum", "avg":     sum or average, excluding dates or strings
    ' "var", "stdev":   sample variance or standard deviation (excluding dates or strings)
    ' "varp", "stdevp": sample variance or standard deviation (excluding dates or strings)
    
    ' You may pass any value in the ParamArray.  Strings, nulls, and dates are ignored for the
    ' sum, avg, stdev, stdevp, var, and varp calculations.  (Thus, you could use this function to
    ' add several columns together without wrapping each column with Nz() to handle nulls...)
    
    Dim Numerator As Double
    Dim Denominator As Double
    Dim Counter As Long
    Dim Result
    
    ' Force to upper case to make sure string comparisons are always performed as expected;
    ' Access uses Option Compare Database by default, but other VBA/VB6 uses binary default
    
    Stat = UCase(Stat)
    
    Select Case Stat
        Case "MIN"
        
            ' Set the result to the first value in the array, then check the other values in
            ' turn to see if it is less.
            
            Result = Vars(LBound(Vars))
            For Counter = LBound(Vars) + 1 To UBound(Vars)
                If IsNull(Result) And Not IsNull(Vars(Counter)) Then
                    Result = Vars(Counter)
                ElseIf Vars(Counter) < Result Then
                    Result = Vars(Counter)
                End If
            Next
        
        Case "MAX"
            
            ' Set the result to the first value in the array, then check the other values in
            ' turn to see if it is greater.
            
            Result = Vars(LBound(Vars))
            For Counter = LBound(Vars) + 1 To UBound(Vars)
                If IsNull(Result) And Not IsNull(Vars(Counter)) Then
                    Result = Vars(Counter)
                ElseIf Vars(Counter) > Result Then
                    Result = Vars(Counter)
                End If
            Next
            
        Case "AVG", "SUM"
        
            ' Check each value in turn.  If it is numeric, then increment numerator and denominator.
            ' Divide numerator by denominator to get an average, or by 1 to get the sum
            
            For Counter = LBound(Vars) To UBound(Vars)
                If IsNumeric(Vars(Counter)) Then
                    Numerator = Numerator + Vars(Counter)
                    Denominator = Denominator + 1
                End If
            Next
            Result = Numerator / IIf(Stat = "AVG", Denominator, 1)
        
        Case "STDEV", "STDEVP", "VAR", "VARP"
        
            ' Take one pass through the set to determine the average, and then determine the
            ' sum of squared deviances from the mean.  Divide by number of elements in the
            ' array for population or (elements - 1) for sample.  If standard deviation,
            ' take square root
            
            For Counter = LBound(Vars) To UBound(Vars)
                If IsNumeric(Vars(Counter)) Then
                    Numerator = Numerator + Vars(Counter)
                    Denominator = Denominator + 1
                End If
            Next
            For Counter = LBound(Vars) To UBound(Vars)
                If IsNumeric(Vars(Counter)) Then Result = Result + (Vars(Counter) - (Numerator / Denominator)) ^ 2
            Next
            Result = Result / IIf(Stat Like "*P", Denominator, Denominator - 1)
            If Stat Like "S*" Then Result = Result ^ 0.5
    End Select
    
    RowStats = Result
    
End Function

Open in new window

0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now