Comparing field values

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

I have a table which contains three date fields (let's
call them Date1, Date2 and Date3), one or two of which may
be blank. I want to add a fourth date field and am trying
to write an update query which will populate the new field
with the earliest date from the other three fields
(ignoring blanks).

Any ideas anyone?
 
Tim

Without know more about your data, it sounds a little like ... a
spreadsheet! Whenever there are repeating fields (Date1, Date2, Date3, ...
DateN), there's a chance that normalization will make your database and job
easier to handle.

Now, what happens if you need to add a fourth field (Date4, say)? You have
to change your table, change your queries, change your forms, change your
expressions, change your reports, ...!!

If you had a single date field (don't call it "Date"! -- that's a reserved
word in Access), you could easily use a query to find the "earliest" date.
When you have multiple fields to check, you have to write an expression that
tests all of them.

On another note -- don't do it! If you can do a calculation to figure out
which is "earliest", don't store that! Once you add the "EarliestDate"
field, and fill it, you'll have to re-synchronize every time someone makes a
correction in any of the fields used in the calculation. Much easier to
build a query that does the calculation, and use that query to see the
earliest dates.
 
You're right. My data is a csv file which I import to
access on a monthly basis to analyse (it's too big for
excel) and a link to the source data is not possible. I
have found a work-around, but it involves doing a series
of update queries and I was hoping there might have been a
simpler way. The file contains approx 1m rows of data.
 
Tim

Consider creating a more normalized structure, and using queries to move
data over from your raw import to the normalized structure. That will make
your querying much easier.
 
Back
Top