Select MIN of 4 Dates

[Tony2021]

Hello Experts,

I have been doing some research online but cant seem to get this right. 

If my dates are D1,D2,D3,D4 from MyTable.

How can I show the MIN of those dates? 

I need to group by [ID]

I also need to account for Nulls. 

I dont know if a function is best or an IIF statement.  

thank you

let me know if need more info.  I tried to keep it simple. 

[#2]

@Tony2021 

1. create a simple query for MyTable

2. View the query in 'design view'

3. Set the column holding your 'date' field to "Descending" (This establishes the priority for the most recent dates) and set the criteria for that column as "Is Not Null" (this will eliminate null values from the query).

4. While still in design view for the query, look for and select the 'Query Design' tab at the top.

5. In the 'Query setup' area, look for the 'Return: All' selection box, and change the 'All' to '5'.

6. This will return the top 5 most recent dates. 

 * You can manually type in the number '1' in the 'Return:' selection box, This will return only the top 1 entry.

[#3]

@Tony2021 

The main problem is an inappropriate table design for a relational database application.

Multiple fields of the same thing, named sequentially. XXX1, XXX2, XXX3, etc. always signal this problem.

This is, by the way, what has to be done in Excel because it lacks the ability to manage data relationally. We're not hampered the same way in Access and shouldn't incorporate inappropriate table designs from Excel.

The way to correct the problem is to properly normalize the tables involved. Here is an excellent series of three or four related blog articles describing the problem in more d...

[#4]

@Kolodgyj 

Hi, thanks for the response.
I have followed as above but I dont completely understand why I would lmite the results to 5. I would remove records that I need. Maybe I am not understanding or maybe I need to explain a bit further.

I have attached the output based on the query paramters above ( but I am showing ALL).

How would I bel able to return the MIN of the date for each ID? 

I assume another query will need to be made.  What is tricky is that I need to remove any dates in the past though so if it is a MIN in the record and if <Today then do not consider it.  

[#5]

This screen shot clearly shows the "spreadsheet" style table problem. It is even shown in Excel, not Access! The solution is as I noted earlier. Correct the table design.

[#6]

HI George,

Ok I understand your point on normalization but wouldnt the problem still exist if it were normalized? I have seen CASE used for this but its beyond my level.
here is an example
https://stackoverflow.com/questions/47965825/sql-query-case-when-min-closest-date-to-today

thank you

[#7]

@Tony2021 

You should have a table with one record for each type of date and a foreign key to the parent LCID.

The table, therefore, has four fields, including its own Primary Key.

PrimaryKey

LCID (Foreign key to the parent table)

DateType or DateSequence (NoticeDate, Expirey1Date, Expiry2Date, UltExpireydate)

SequenceDate

Now, a query against this table, using Min or Max for SequenceDate, returns the earliest or latest date for each LCID.

SELECT LCID, Min(SequenceDate) as EarliestDate

FROM tblofExpireyDates

GROUP BY LCID 

or

SELECT LCID, Max(SequenceDate) as LastestDate

FROM tblofExpireyDates

GROUP BY LCID 

That would have to be joined back to the parent same table to get the appropriate DateSequence that corresponds to that LCID and date.

SELECT LCID, EarliestDate, DateSequence

FROM tblofExpireyDates INNER JOIN qryofEarliestDates

On tblofExpireyDates.LCID = qryofEarliestDates.LCID and tblofExpireyDates.SequenceDate = qryofEarliestDates.EarliestDate

or 

SELECT LCID, LastestDate, DateSequence

FROM tblofExpireyDates INNER JOIN qryofLatestDates

On tblofExpireyDates.LCID = qryofLatestDates.LCID and tblofExpireyDates.SequenceDate = qryofLatestDates.EarliestDate

[#8]

@Tony2021 

I agree with Normalization, but if you are unable to do it, you can still get the Min Date by calling

a UDF (user defined function) from your Query.

See Module1 for the function.

Open Query1 that uses this function.

[#9]

@arnel_gp 

HI Arnel, you are correct that I am not able to Normalize at this point. It would require a lot of rework. I know of this concept but didnt think to apply it to dates. I know this now so a good lesson brought up by George.

I have looked at the db udf and query. It looks great & performs as intended. 

I did leave out a point since I was going to figure out a solution on my own but the function I do not understand how to modify and kindly ask your assistance.

What I need now is to return the MIN but I need to consider the current date.

I will try to summarize in english:
(1) If all dates are >Date() ===>return the MIN (as the UDF does now)
(2) Exclude any dates that are <Date() however if all Dates are <current date then return the MIN of those dates that are <current date. 

I hope its not confusing. Grateful for the assistance.  thank you in advance.  Happy to add more detail if needed. 

[#10 · Accepted solution]

what do you mean by "current date"? isn't it the same with Date()?

see if the Conditions are met in the new function.

[#11]

wow that was perfect! Tested and returns exactly as described. thank you very much! Have a good day Arnel / George and thank you for the great assistance and tips about normalization.