Query to find the MIN value of multiple date fields

[chrisline90]

HI,

I am trying to write a query that includes up to 6 individual dates from a table (or tables) and I want to find the MIN value of all dates in a record, So for example:

The [NextExpiry] would look at the 6 dates and show the MINIMUM of those dates to indicate when the next ‘element’ was due to expire.

I have tried using the IIF function but for up to 6 dates, I can’t find a way to make it work.

Additionally, not all 6 dates are necessarily used. Some records may only have 3 or 4 of the 6 dates, so the query needs to disregard blank fields and just return the MIN date of the dates available in each record.

If anyone can offer advice, I would be very grateful.

 

[DHookom]

This is an issue when your table structure isn’t normalized. You can create a union query of all the date fields with the primary key and then a totals query based on the union query.

 

[GPGeorge]

HI,

I am trying to write a query that includes up to 6 individual dates from a table (or tables) and I want to find the MIN value of all dates in a record, So for example:

View attachment 115969

The [NextExpiry] would look at the 6 dates and show the MINIMUM of those dates to indicate when the next ‘element’ was due to expire.

I have tried using the IIF function but for up to 6 dates, I can’t find a way to make it work.

Additionally, not all 6 dates are necessarily used. Some records may only have 3 or 4 of the 6 dates, so the query needs to disregard blank fields and just return the MIN date of the dates available in each record.

If anyone can offer advice, I would be very grateful.

Along the same lines as Duane's response, non-normalized tables like this add an extra layer of ongoing complexity to the process. It's a simple matter, however, to correct the flawed table design, although in a mature database, that can lead to a lot of additional corrective re-design. Therefore, while I recommend you correct the flawed table, it may be something that you may want to live with in the short-term rather than incur the effort to correct it.

The table in the screen shot, fortunately, already appears to be a child table of the Employee table. That mininizes a lot of the interface redesign, although it doesn't eliminate it.

The properly normalized ExpiryDates table will have fourfields:

ExpiryDateID -- an AutoNumber -- this is the primary key for the table
EmployeeID -- a Long Integer -- this is the foreign key to the Employee table to related relate each Employee to their ExpiryDates
ExpiryReasonID -- a Long Integer -- this the foreign key to a new ExpiryReason lookup table which lists all of the possible reasons for an Expiration.
ExpiryDate -- a Date -- this is the Date for the corresponding expiration reason for that Employee

With that design, an aggregate query using Max(ExpiryDate) and Grouping on EmployeeID returns the required date for each employee.

 

[June7]

Otherwise, use a custom VBA function to find the minimum of 6 fields. Review https://www.access-programmers.co.uk/forums/threads/max-of-multiple-columns.184618/

 

[Gasman]

You really should correct your structure.
However you could use Alan Browne's functions
You will likely need to use NZ() and a high date for your null fields.

 

[chrisline90]

Thank You all for your advice. Very much appreciated. I will revisit the table structure.

 

[CJ_London]

using Max(ExpiryDate)

Think you mean Min

 

[GPGeorge]

Think you mean Min

Hm. That is what the OP said, isn't it?

 

[Gasman]

Hm. That is what the OP said, isn't it?

George,
See last paragraph of post #3

 

[GPGeorge]

George,
See last paragraph of post #3

It's also in the post title.

 

[Gasman]

You have lost me.
The request is Min as you just mentioned, yet you post

With that design, an aggregate query using Max(ExpiryDate) and Grouping on EmployeeID returns the required date for each employee.

which I thought @CJ_London was referring to?

 

[GPGeorge]

You have lost me.
The request is Min as you just mentioned, yet you post

which I thought @CJ_London was referring to?

Okay, for the sake of clarity.

In Post #8 I acknowledged that the OP had said "Min" not max. "Hm. That is what the OP said, isn't it?" If you misinterpret that, I apologize. I thought I was quite clearly recognizing that the OP had used "Min".

In Post #10, I again noted that the OP had specified "Min" in the Post title. Again, if you misinterpret that, I apologize again. I thought I was quite clearly recognizing that the OP had used "Min".

 

[June7]

George, CJ directed you to post 3 where you used Max(ExpiryDate), not Min.

 

[GPGeorge]

George, CJ directed you to post 3 where you used Max(ExpiryDate), not Min.

Good grief. And I acknowledged the error. More than once.

How many times are you two going to repeat this?

 

[June7]

Sorry, I didn't realize you had acknowledged the misdirect in post 3.

 

[CJ_London]

It’s the opposite side to a two headed coin

 

[Gasman]

Sorry, I didn't realize you had acknowledged the misdirect in post 3.

Nor did I.
My apologies.