update a column with an earliest date from four columns

[gsrai31]

Hi, I want to update a column called "end_date", in table "inp_Data", with an earliest date available in these four column:

"F_date", or
"G_date", or
"H_date", or
"I_date",

If all four columns are blank then update "end_date" to text "No date".

Can this be done in a single update query? I am using Access 2003.
Thanks in advance. gsrai31

 

[June7]

There is no intrinsic function to pull the 'earliest' or 'latest' value from multiple fields. The more values to compare, the more complicated. Easiest approach may be a VBA custom function. This is a fairly common topic.

You could call the function from an UPDATE query but there really is no reason to save this calculated value - calculate when needed.

Review http://allenbrowne.com/func-09.html

Alternatively, normalize data structure with a related table where each date would be a separate record and can use aggregate or TOP N query. Or a UNION query can rearrange data to normalized structure and use that query as source for aggregate or TOP N. However, again, instead of saving the value, just calculate when needed.

 

[MajP]

Can this be done in a single update query?

Yes. Union the for fields together in a union query. Then use that in a aggregate query taking the min of the date field. Then you have the answer no need to do an update.

 

[The_Doc_Man]

gsrai31, this wasn't mentioned directly (though June7 mentioned it in passing). That data structure is not fully normalized. You have what is called a "repeating group" (in this case, four dates in the same record) that violate a normalization rule.

A normalized table is one for which every field depends on one and only one value - that value being the prime key or PK. If you look at the four dates, they are somehow related (because you are willing to take a particular date from any one of the fields). But for those four date fields, something else besides the PK is required to select one of them. (That is a characteristic of a "repeating group".) There is an implied selector NOT RELATED TO THE PK that governs those four dates, and that is the normalization violation. As suggested by June7 (and implied by MajP, since his suggestion would normalize the dates), your problem would be very different if you made that table normalized.

If you are not familiar with normalization, please look it up and do a little bit of reading. In this forum, the SEARCHE function is in the thin blue ribbon at the top of each page, just below the box where your login name is displayed. SEARCH is 3rd from the right.

If you search the forum, look up "normalization." If on the other hand you search the web, search for "database normalization" because if you don't, you will get articles on political normalization, mathematical normalization, and chemical normalization. Plus quite a few other topics.

 

[arnelgp]

also this will be your Update query:

Update inp_Data Set end_Date=Switch(F_date & G_date & H_date & I_date & "")="", "No date", Nz(F_date, 0) < Nz(G_date, 0) And Nz(F_date, 0) < Nz(H_date, 0) And Nz(F_date, 0) < Nz(I_date, 0), F_date, Nz(G_date, 0) < Nz(F_date, 0) And Nz(G_date, 0) < Nz(H_date, 0) And Nz(G_date, 0) < Nz(I_date, 0), G_date, Nz(H_date, 0) < Nz(G_date, 0) And Nz(H_date, 0) < Nz(F_date, 0) And Nz(H_date, 0) < Nz(I_date, 0), H_date, Nz(I_date, 0) < Nz(G_date, 0) And Nz(I_date, 0) < Nz(H_date, 0) And Nz(I_date, 0) < Nz(F_date, 0), I_date)

 

[gsrai31]

Hi June7, MajP, The Doc Man, thank you very much for your suggestions, really appreciate your help. Update query by arnelgp worked for me. It is perfect apart from updating text "No date" in date/time field. which is fine. Thank you very much arnelgp. Instead of "No date" is it possible to update blanks to 00/01/1900 where there are all four fields are blank?

 

[arnelgp]

you can replace the "No date" on the Expression with #01/01/1900#.
00/01/1900 is an invalid date as you may know.

 

[gsrai31]

arnelgp, I get syntax error in date in query expression.

 

[arnelgp]

if end_date is (text):

Update inp_Data Set end_Date=Switch(F_date & G_date & H_date & I_date & "")="", "1/1/1900", Nz(F_date, 0) < Nz(G_date, 0) And Nz(F_date, 0) < Nz(H_date, 0) And Nz(F_date, 0) < Nz(I_date, 0), F_date, Nz(G_date, 0) < Nz(F_date, 0) And Nz(G_date, 0) < Nz(H_date, 0) And Nz(G_date, 0) < Nz(I_date, 0), G_date, Nz(H_date, 0) < Nz(G_date, 0) And Nz(H_date, 0) < Nz(F_date, 0) And Nz(H_date, 0) < Nz(I_date, 0), H_date, Nz(I_date, 0) < Nz(G_date, 0) And Nz(I_date, 0) < Nz(H_date, 0) And Nz(I_date, 0) < Nz(F_date, 0), I_date)

if end_date is date/time:

Update inp_Data Set end_Date=Switch(F_date & G_date & H_date & I_date & "")="", #1/1/1900#, Nz(F_date, 0) < Nz(G_date, 0) And Nz(F_date, 0) < Nz(H_date, 0) And Nz(F_date, 0) < Nz(I_date, 0), F_date, Nz(G_date, 0) < Nz(F_date, 0) And Nz(G_date, 0) < Nz(H_date, 0) And Nz(G_date, 0) < Nz(I_date, 0), G_date, Nz(H_date, 0) < Nz(G_date, 0) And Nz(H_date, 0) < Nz(F_date, 0) And Nz(H_date, 0) < Nz(I_date, 0), H_date, Nz(I_date, 0) < Nz(G_date, 0) And Nz(I_date, 0) < Nz(H_date, 0) And Nz(I_date, 0) < Nz(F_date, 0), I_date)

 

[gsrai31]

Hi, end_date is date/time type, however it doesn't like #1/1/1900#, I get syntax error in update statement error. It okay for it to be left blank if it is easier.

 

[Galaxiom]

Update query by arnelgp worked for me. It is perfect apart from updating text "No date" in date/time field. which is fine.

As you noted, the dates in the updated field would be stored as text which is never a good idea. Secondly you are further denormalizing by storing this field in the table at all.

Most importantly, you have a serious data structure error. Read what Docman advised.

Continuing to develop your database using the existing structure and updating another denormalized field with the latest date from the other fields is prone to ending up with the the wrong value through missing an update. Denormalizing will add to the problems you encounter in the future and you will have to keep coming up with clumsy work-arounds like the one that prompted your question and the huge inefficient expression posted by arnlegp.

The dates should be in a related table linked to the main records by a key field. Another field holds the date with a separate record for each date. Another field stores the F, G H or I designation. Returning the latest date then becomes a simple efficient Max aggregate query grouped by the key.

Instead of "No date" is it possible to update blanks to 00/01/1900 where there are all four fields are blank?

The absence of data is best stored as what it is, a Null. What you display in the forms and reports is a formatting issue, not a storage issue.

 

[arnelgp]

change it to:

Update inp_Data Set end_Date=Switch((F_date & G_date & H_date & I_date & "")="", #1/1/1900#, Nz(F_date, 0) < Nz(G_date, 0) And Nz(F_date, 0) < Nz(H_date, 0) And Nz(F_date, 0) < Nz(I_date, 0), F_date, Nz(G_date, 0) < Nz(F_date, 0) And Nz(G_date, 0) < Nz(H_date, 0) And Nz(G_date, 0) < Nz(I_date, 0), G_date, Nz(H_date, 0) < Nz(G_date, 0) And Nz(H_date, 0) < Nz(F_date, 0) And Nz(H_date, 0) < Nz(I_date, 0), H_date, Nz(I_date, 0) < Nz(G_date, 0) And Nz(I_date, 0) < Nz(H_date, 0) And Nz(I_date, 0) < Nz(F_date, 0), I_date)

 

[gsrai31]

Galaxiom - thank you. I understand. It is not a structured database, I am just working out a quick way to manipulate a large amount of data in Access as Excel cannot handle and keeps freezing. All I am trying to do is quickly import a large amount of data in a table and add some new fields, some quick calculations and then export small data set into Excel for operational reporting. Next time delete all the data and import new data and then repeat the processes.

arnelgp - thanks for the updated query. The issue I now have is that it only updates records where all 4 fields are either null or all 4 not null. If only 1 or 2 or 3 fields have date values, it ignores those records. I need to pick up earliest dates from these too. thanks

 

[Gasman]

So why not import the data in the suggested format and then, as mentioned this would be so much easier.?

Galaxiom - thank you. I understand. It is not a structured database, I am just working out a quick way to manipulate a large amount of data in Access as Excel cannot handle and keeps freezing. All I am trying to do is quickly import a large amount of data in a table and add some new fields, some quick calculations and then export small data set into Excel for operational reporting. Next time delete all the data and import new data and then repeat the processes.

arnelgp - thanks for the updated query. The issue I now have is that it only updates records where all 4 fields are either null or all 4 not null. If only 1 or 2 or 3 fields have date values, it ignores those records. I need to pick up earliest dates from these too. thanks

 

[isladogs]

I think that, despite arnel's efforts to assist, trying to do this in one query is making things more difficult rather than easier.
Also if you ever add yet another date column J_Date, the whole query will need rewriting

Have a look at this alternative approach which is basically a combination of what was suggested in posts 2 & 3.
It will work whether you have one or more nulls & also if all values are null.
It would still work with a 'J_date' column if necessary

I've created a procedure that runs 3 queries in turn:
a) Union query
b) Aggregate query to append the earliest dates from the union query to a 'temp' table tblMinDate.
This is necessary as the update query can't be based on an aggregate query
c) Update query using values from tblMinDate

One button click on the form runs the procedure.
Another clears the End_Date column (if needed)

HTH

 

[The_Doc_Man]

I'll contribute one more comment. Instead of #1/1/1900# as a date, use CDate(0). After all, 1 Jan 1900 IS the reference date. (Or is it 31 Dec 1899?) Excel and Access reference dates are one day apart and I can NEVER remember which one uses which. Doesn't matter, though. Just use the dates consistently and you'll be fine.

 

[isladogs]

I'll contribute one more comment. Instead of #1/1/1900# as a date, use CDate(0). After all, 1 Jan 1900 IS the reference date. (Or is it 31 Dec 1899?) Excel and Access reference dates are one day apart and I can NEVER remember which one uses which. Doesn't matter, though. Just use the dates consistently and you'll be fine.

Neither of the above. In Access, day zero is 30 Dec 1899
Also if you don't specify a date format to ignore times, CDate(0) will just be shown as 00:00:00.

I would leave null dates as null as I deliberately did in my example.

 

[gsrai31]

Hi The_Doc_Man, arnel, thank you very much for your help and suggestions.
isladogs - I will try your procedure. thank you
Meanwhile I managed to achieve the result with 4 update queries, so updating end_date with the earliest dates from each date field, where it is not null. It is not ideal, but it is fine and fit for purpose for the time being. Thanks

 

[arnelgp]

ok, reverse it to something bigger date:

UPDATE inp_Data SET inp_Data.end_Date = Switch((F_date & G_date & H_date & I_date & "")="",#1/1/1900#,Nz(F_date,#12/31/9999#)<Nz(G_date,#12/31/9999#) And Nz(F_date,#12/31/9999#)<Nz(H_date,#12/31/9999#) And Nz(F_date,#12/31/9999#)<Nz(I_date,#12/31/9999#),F_date,Nz(G_date,#12/31/9999#)<Nz(F_date,#12/31/9999#) And Nz(G_date,#12/31/9999#)<Nz(H_date,#12/31/9999#) And Nz(G_date,#12/31/9999#)<Nz(I_date,#12/31/9999#),G_date,Nz(H_date,#12/31/9999#)<Nz(G_date,#12/31/9999#) And Nz(H_date,#12/31/9999#)<Nz(F_date,#12/31/9999#) And Nz(H_date,#12/31/9999#)<Nz(I_date,#12/31/9999#),H_date,Nz(I_date,#12/31/9999#)<Nz(G_date,#12/31/9999#) And Nz(I_date,#12/31/9999#)<Nz(H_date,#12/31/9999#) And Nz(I_date,#12/31/9999#)<Nz(F_date,#12/31/9999#),I_date);

 

[gsrai31]

Perfect, it worked, thank you very much for your help, arnelgp. It does exactly what I wanted it do.
Now the next battle is to calculate number of days between start_date and end_date, excluding Sat, Sun, bank holidays and the start day. I probably need to start a new thread as this one is solved. I will try finding what is already out there. Thanks again for everyone's help, happy new year.