Find the earliest date from multiple dates in a row

grampygrumpy

New Member
Joined
Jan 30, 2017
Messages
9
My Access database has 9 columns that contain a date. I need to select the earliest date in each row. Note: All columns in some rows may not have a date. In those cases, I will leave the results field blank. Thank you,
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
What do these 9 dates represent?

It sounds like you are working with data that is not normalized. Typically, you do not have similar repeating fields in a single record (row), instead you would have nine records with one field.
The Access Aggregate functions, like Min and Max are designed to work on a single field across multiple records. If your data is structured like that, it is very easy to do. If not, then it becomes much more cumbersome. You either have to compare all the fields, one-by-one, or create a User Defined Function, that does much the same.

So, to give you an example of what Normalized data would look like, let's say that we have an example of tracking the number of births by State by month. In a data structure that wasn't normalized, we might have a Field for State, and then 12 fields, one for each month. It may look right, but this is not a normalized structure that makes the data very hard to work with.

In a Normlized structure, your table would have a Field for State, a Field for Month, and then the total births for that Month. So each state would have 12 records, one for each month.
Now, if we wanted to get the Min or Max values, it is very easy to do so, with the Aggregate functions.
 
Upvote 0
Joe4, thanks for the response. I will try to clarify. Maybe I should have pointed out that each of the fields is identified with a different field name. I am retrieving equipment and the client considers the equipment retrieved successfully as long as there is a date in one of the 9 columns. In some rows, there will be dates in more than one field. I need the best date in a row.
 
Upvote 0
Joe4, thanks for the response. I will try to clarify. Maybe I should have pointed out that each of the fields is identified with a different field name. I am retrieving equipment and the client considers the equipment retrieved successfully as long as there is a date in one of the 9 columns. In some rows, there will be dates in more than one field. I need the best date in a row.
I think it still should follow the same rule. I think I can make it make more sense to you if you can show me a sample of the data.
 
Upvote 0
Here is a sample of the data. First time adding data to the forum so let me know if I missed a step. The preview looked okay.

Rows Multi-Receipt Dates.xlsx
ABCDEFGHIJKL
1PIDDateShippedRecDate1RecDate2RecDate3RecDate4RecDate5RecDate6RecDate7RecDate8DivisionCountry
27721/1/20191/4/20191/14/20191/17/20191/15/2019NAMUnited States
37721/1/20191/9/20191/14/20191/17/20191/15/2019NAMUnited States
47721/2/20191/8/20191/10/2019NAMUnited States
57721/2/20191/9/20191/14/2019NAMUnited States
67721/2/20191/8/20191/15/2019NAMUnited States
77721/2/20191/10/20191/15/2019NAMUnited States
87721/2/20191/17/20191/22/20191/29/20191/24/2019NAMUnited States
97721/2/20191/16/20191/23/20191/29/20191/25/2019NAMUnited States
107721/2/20191/16/20191/23/20191/29/20191/25/2019NAMUnited States
117721/2/20191/13/20192/4/20191/29/2019APACSingapore
127721/2/20192/20/20192/19/2019APACAustralia
137721/2/20191/29/20191/31/2019APACAustralia
147721/2/20191/7/20191/18/20191/16/2019EMEAUnited Kingdom (GB)
157721/2/20192/1/20192/4/2019LATAMMexico
167721/2/20191/9/20191/10/2019APACHong Kong
177721/2/20191/24/20141/9/2019LATAMMexico
187721/2/20191/8/20191/9/2019EMEAIreland
197721/2/2019EMEASweden
207721/2/2019NAMUnited States
217721/2/2019NAMUnited States
227721/3/20192/11/20193/1/2019NAMUnited States
237721/3/20191/7/20191/10/20191/11/20191/10/2019NAMUnited States
247721/3/20191/9/20191/10/20191/15/20191/11/2019NAMUnited States
Query1
 
Upvote 0
Yes, it appears that you do have a Data Normalization issue.
You should not have 8 fields for "RecDate". You should only have 1 field for "RecDate", and then multiple records.
If you need to distinguish by the a number, you can also have a "RecDate" number field.

Here is a simplified illustration. So instead of having a structure like this:
Query1

PIDRecDate1RecDate2RecDate3
772​
1/1/2020​
1/2/2020​
1/3/2020​
773​
1/1/2020​
2/1/2020​
You should have a structure like this:
Query2

PIDRecDateNumRecDate
772​
1​
1/1/2020​
772​
2​
1/2/2020​
772​
3​
1/3/2020​
773​
1​
1/1/2020​
773​
2​
2/1/2020​
Having a Normalized structure like this makes it very easy to get the most recent RecDate for each PID, like this:
Query3

PIDMaxOfRecDate
772​
1/3/2020​
773​
2/1/2020​
The code for that Aggregate Query looks like this:
VBA Code:
SELECT TableName.PID, Max(TableName.RecDate) AS MaxOfRecDate
FROM TableName
GROUP BY TableName.PID;
(this can also be built pretty easily using the Query Builder).
 
Upvote 0
Absolutely needs to be normalized for just the reasons stated - this is how databases work.

Alternatives are:
1) One big huge very ugly formula (I tried and gave up but should be possible)
2) Dump the data into Excel and do the work there (Excel can work with data like this - in fact your data looks like something somebody would do in a spreadsheet.
3) My proposed workaround below

My proposed workaround:
1) create a new field in your table called NewTempField (or whatever you like). It should be a datetime field, which I assume all your DateRec fields also are
2) Run the queries below each in turn:

Code:
update MyTable
set NewTempField = IIf(Nz(RecDate1,#1/1/1900#)>Nz(RecDate2,#1/1/1900#),Nz(RecDate1,#1/1/1900#),Nz(RecDate2,#1/1/1900#))

update MyTable
set NewTempField = IIf(NewTempField>Nz(RecDate2,#1/1/1900#),NewTempField,Nz(RecDate2,#1/1/1900#))

update MyTable
set NewTempField = IIf(NewTempField>Nz(RecDate3,#1/1/1900#),NewTempField,Nz(RecDate3,#1/1/1900#))

update MyTable
set NewTempField = IIf(NewTempField>Nz(RecDate4,#1/1/1900#),NewTempField,Nz(RecDate4,#1/1/1900#))

update MyTable
set NewTempField = IIf(NewTempField>Nz(RecDate5,#1/1/1900#),NewTempField,Nz(RecDate5,#1/1/1900#))

update MyTable
set NewTempField = IIf(NewTempField>Nz(RecDate6,#1/1/1900#),NewTempField,Nz(RecDate6,#1/1/1900#))

update MyTable
set NewTempField = IIf(NewTempField>Nz(RecDate7,#1/1/1900#),NewTempField,Nz(RecDate7,#1/1/1900#))

update MyTable
set NewTempField = IIf(NewTempField>Nz(RecDate8,#1/1/1900#),NewTempField,Nz(RecDate8,#1/1/1900#))

update MyTable
set NewTempField = null
where NewTempField = #1/1/1900#

This is also long but not entirely ugly since it's easy to see the logic and less likely to be a problem if you need to update it, whereas a big formula can be a real bear to get right. You would want to run these with a macro so that you can be sure all of them are run in turn without missing any.
 
Last edited:
Upvote 0
Solution
Joe4 - Thanks for the information. Unfortunately, I keep finding that I do not know as much about Access as I think I do. Is Query Builder the same as Expression Builder. I do not know VBA. I did not show it but there is a field where the number is unique for each row. How you add the RecDate to the query so they are vertical instead of horizontal. The database has approx 1500 rows. Sorry if my questions seem foolish. I am going to give xenou's suggestion a try but would also like to understand and be able to use your approach. I appreciate the help.

xenou - Thanks for providing an alternative solution. I will create the Macro and give it a try. I have previously had programs where I wanted to get the earliest of three dates and have used the following which one of my associates had developed and I was able to modify. It would be difficult to extend this to 8 dates.

DateReceived: IIf([tblData]![DateReceived] Is Null,[tblData]![ConfirmedRestartDate],IIf([tblData]![ConfirmedRestartDate] Is Null,[tblData]![DateReceived],IIf([ConfirmedRestartDate]<=[tblData]![DateReceived],[ConfirmedRestartDate],[tblData]![DateReceived])))

Thank you both
 
Upvote 0
Joe4 - Thanks for the information. Unfortunately, I keep finding that I do not know as much about Access as I think I do. Is Query Builder the same as Expression Builder.
No. The Query Builder is where you go to if you go to the Create menu, and select "Query Design".

I did not show it but there is a field where the number is unique for each row.
That's fine, but it has no impact on the problem you are facing. The issue is not that you don't have a unique primary key, the issue is that you have repeating similar fields in your Access table.

How you add the RecDate to the query so they are vertical instead of horizontal. The database has approx 1500 rows.
If you don't know VBA, it is going to be cumbersome. You need need to create an Append Query for each of your fields to add all the new records to the new table. Quite frankly, I would probably prefer to export the data to Excel, and manipulate it there and import it back into Excel.
 
Upvote 0
What you could also do, is combine the desired data onto one table and filter from there.

It's unfortunate that your table does not have a unique identifier, so this will require an additional step to get a unique number.

Below are thesteps:
1) Create a table with unique reference number;
2) Create the normalized table layout.
3) Create formulas needed to populate the normalized table;
4) Create a macro to combine the formulas;
5) Create a formula to get the desired results.


1) Create a table with unique reference number;
First make a copy of your table's layout. The data in it is not needed yet. The only change is that a column with auto-numbering should be added. In the below example, this table will be called TBL_Temporary_Table_1 and the extra column will be called Temporary_Reference.

Then create a query that deletes all contents from TBL_Temporary_Table_1. That's pretty straight forward. I have called this formula ADD_Temporary_table:
VBA Code:
DELETE TBL_Temporary_Table_1.*
FROM TBL_Temporary_Table_1;

And create a query that copies the contents of your table to TBL_Temporary_Table_1. Again, pretty straight forward. For reference, I have called this formula ADD_Temporary_table.
VBA Code:
INSERT INTO TBL_Temporary_Table_1
SELECT Table1.*
FROM Table1;



2) Create the normalize table layout
This table I called TBL_Temporary_Table_2. It contains the following fields:
  • Temporary_Reference
  • PID
  • RecDate
  • Source



3) Create formulas needed to populate the normalized table;
You now need to create 9 append queries, one for each column with dates. These append queries are called ADD_Temporary_Table_2_1 through ADD_Temporary_Table_2_9, and copy information from table TBL_Temporary_Table_1 into table TBL_Temporary_Table_2.

I will only copy one of the VBA codes.
VBA Code:
INSERT INTO TBL_Temporary_Table_2[COLOR=rgb(41, 105, 176)] [/COLOR]( Temporary_Reference, PID, RecDate, Source )
SELECT TBL_Temporary_Table_1.Temporary_Reference, TBL_Temporary_Table_1.PID, TBL_Temporary_Table_1.RecDate1, "RecDate1" AS Source
FROM TBL_Temporary_Table_1;



4) Create a macro to combine the formulas;
SetWarnings (No)
OpenQuery (DEL_Temporary_Table)
OpenQuery (ADD_Temporary_table)
OpenQuery (ADD_Temporary_table_2_1)
...
OpenQuery (ADD_Temporary_table_2_9)
SetWarnings (Yes)



5) Create a formula to get the desired results.
Create a formula based on the normalized table TBL_Temporary_Table_2 to determine the minimum date.
This query I called QRY_Temporary_Table_2.

VBA Code:
SELECT TBL_Temporary_Table_2.Temporary_Reference, TBL_Temporary_Table_2.PID, Min(TBL_Temporary_Table_2.RecDate) AS MinOfRecDate
FROM TBL_Temporary_Table_2
GROUP BY TBL_Temporary_Table_2.Temporary_Reference, TBL_Temporary_Table_2.PID;


If you also need to know from which column the mimum date is extracted, you could add this query as well.
VBA Code:
SELECT QRY_Temporary_Table_2.*, TBL_Temporary_Table_2.Source
FROM QRY_Temporary_Table_2 LEFT JOIN TBL_Temporary_Table_2 ON (QRY_Temporary_Table_2.MinOfRecDate = TBL_Temporary_Table_2.RecDate) AND (QRY_Temporary_Table_2.PID = TBL_Temporary_Table_2.PID) AND (QRY_Temporary_Table_2.Temporary_Reference = TBL_Temporary_Table_2.Temporary_Reference);
 
Last edited:
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,788
Members
448,297
Latest member
carmadgar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top