earliest date?

[Danny Boy]

Each record in my query has 6 date fields - D1, D2, D3
etc. I would like to define a new field to be
called "D_earliest" that will be the earliest of the 6
date fields. The earliest date can be located in any of
the 6 possible date fields D1 through D6.

I should also add that each record does always contain
the 6 date fields. Some records contain only 3 date
fields (D1, D3, D5), with the other date fields being
null values. I want to define the "D_earliest" field to
pick the earliest of the available date fields.

I hope this makes sense and would greatly appreciate any
assistance offered. Many thanks.

 

[John Vinson]

Each record in my query has 6 date fields - D1, D2, D3
etc.

Then your table violates third normal form. A much better design would
be a one-to-many relationship to a table with ONE date field - if you
need three dates you'ld have three records, if you need six you'ld
have six - and if you need *seven*, you can add a seventh record
easily. You certainly can't in your present design! You would also be
able to use the Min() Totals query clause to easily find the earliest
date.

I would like to define a new field to be
called "D_earliest" that will be the earliest of the 6
date fields. The earliest date can be located in any of
the 6 possible date fields D1 through D6.

I should also add that each record does always contain
the 6 date fields. Some records contain only 3 date
fields (D1, D3, D5), with the other date fields being
null values. I want to define the "D_earliest" field to
pick the earliest of the available date fields.

I hope this makes sense and would greatly appreciate any
assistance offered. Many thanks.

With your current non-normal design you'll probably want to write a
custom VBA function, pass it all the dates, and loop through them and
find the smallest non-NULL date.

 

[Daniel Attard]

John, thank you for taking the time to respond to my
question. I'm not sure I completely understand what you
are saying. What do you mean by a one-to-many
relationship to a table with ONE date field? What is on
the many side of the relationship? When you say that
if i need three dates I would have three records, don't
you mean I would have three "fields"?

The reason I have 6 date fields is because each date
field is a different type. Not only are there different
types of date fields, there are also different years. I
guess there is no reason why the different years have to
be separated into different tables. If all years were in
one table (with an additional field to designate the
year), I could still have the 6 different date field
types. Does this make sense as an appropriate
structure? Thank you for your help.

 

[John Vinson]

John, thank you for taking the time to respond to my
question. I'm not sure I completely understand what you
are saying. What do you mean by a one-to-many
relationship to a table with ONE date field? What is on
the many side of the relationship? When you say that
if i need three dates I would have three records, don't
you mean I would have three "fields"?

I'm talking about two tables and no, I don't mean three fields.

YourTable
YourID
<other fields, but no dates>

DateTable
YourID <link to YourTable>
DateType <one of six values>
TheDate

YourID and DateType would be a joint, two-field Primary Key.

Not knowing what the date types might be, I'll hazard a guess for some
sample data in DateTable:

UniqueID DateType TheDate
123 Originate 11/14/2003
123 Specifications 12/9/2003
123 Initiate Build 12/11/2003
123 Inspection 1/4/2004

One datefield; four records; four dates for item 123.

The reason I have 6 date fields is because each date
field is a different type. Not only are there different
types of date fields, there are also different years. I
guess there is no reason why the different years have to
be separated into different tables. If all years were in
one table (with an additional field to designate the
year), I could still have the 6 different date field
types. Does this make sense as an appropriate
structure? Thank you for your help.

Even less sense. A date field CONTAINS a year. The date 12/4/2003 and
the date 12/4/2004 are two different dates!

It would seem that (perhaps not surprisingly given the paucity of
information you've posted about the meaning of all this) that I'm
completely missing the point of what these dates are and how they're
used!