How to query this?

[TA]

Hello all,
I have a table have 3 char fields: date1,date2,date3.
How to I do a query to know which field is contain the
oldest date? Like:
Date1 Date2 Date3
01/11/2003 <NULL> 01/13/03
These fields sometime have a value sometime is null.
How can I determine it have a value to calculate?
Thank for all reply,
TA

 

[Tom Ellison]

Dear TA:

As a rule, having 2 or more columns in a table that are sufficiently
similar that you would want to compare them is bad table design. They
should be in 3 separate rows, perhaps in an additional table. Your
problem would then be much simpler.

Using what you have, you might do something like this:

IIf(Nz(Date1, #12/31/2999# < IIf (Nz(Date2, #12/31/2999# < Nz(Date3,
#12/31/2999#), Nz(Date2, #12/31/2999#), Nz(Date3, #12/31/2999#)),
Nz(Date1, #12/31/2999#), IIf (Nz(Date2, #12/31/2999# < Nz(Date3,
#12/31/2999#), Nz(Date2, #12/31/2999#), Nz(Date3, #12/31/2999#)))

The above will show the earliest of the 3 dates, or 12/29/2999 if all
3 are NULL.

This is the kind of mess created by poor table design. In addition,
if there were ever the need for additional dates to compare, the mess
will grow geometrically. With proper table design, the code would not
even change for additional dates.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[TA]

Thank you Tom,
You R right. The table have about 10 columns and how is
the problem will easy than, could you point out for me.
Best Regards,
TA

 

[Tom Ellison]

Dear TA:

So, you have 10 dates, not just 3. Is that the case?

To advise you, I'll first ask some questions:

- What do these dates represent? Are they 10 consecutive identical
events, or 10 quite different events? Is there some kind of "label"
that differentiates one from another? Or are they entered in no
particular sequence and undifferentiated other than by date?

- Could there ever be a need for more than 10 of them? Are there
varying numbers of them associated with each row in your current
table?

- Are there other columns of information in the current table? I
expect there are "Identifying" columns which probably make up some
unique key. But is there other data involved?

What I will probably recommend is either:

- Have only one date in the current table, and many rows for the up to
10 dates.

- Move the dates to another table, again with only one date per row.

In either case, we can "label" each date if your information requires
it.

Once you've done this, as MIN() across each group (set if identifiying
keys) will quickly give you the earliest date of the set. It may be
there won't be any NULLs to contend with, since the potential dates
simply won't be added to the table.

But, I don't want to get ahead of myself here. Please try to answer
all the questions and supply and additional relevant detail so we can
move ahead soon.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[TA]

Many thanks Tom for take your time,
Here my table design:
Clientid - PK, Integer, 10
Sex - Char,1
Ethnic - C,1
DOB - C,10,
City C,30
State -C,2
Zip C,5
Datevisit1 -C,10
Datevisit2 -C,10
Datevisit3 -C,10

The data entry person will enter following format for date
visit like: mm/dd/yyyy"
So, whenever done for data entry I need to pull out info.
from that clientid what is the latest date visit of those
clientid to import to another table.
Hope there are enough infomation for you,
Best Regards,
TA

 

[Tom Ellison]

Dear TA:

Sounds like you should have a separate table, eliminating Datevisit#
columns from this table. That table would be:

Clientid
Datevisit

The PK could be these two columns together, unless you anticipate
allowing the recording of 2 visits on the same date.

If this table is called VisitDate then the query would be:

SELECT Clientid, MAX(Datevisit) AS RecentVisit
FROM Datevisit
GROUP BY Clientid

If you save this query and join it to any other query, you can get the
date of a client's most recent visit, assuming the client has at least
one visit recorded.

On a form, you can now list the visits vertically in another subform
instead of horizontally. This removes any restriction on how many
visits may be recorded. Also, they can be displayed in sorted order.
When they are in columns, they cannot be sorted if they become out of
sequence for any reason.

There are thus many reasons why this is the best way to design
databases. Many tables, few columns, and great flexibility.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[TA]

Dear Tom,
Your suggesion is perfect.
I doing this way now. Again, thanks a lot for taking your
time to help me out.
Best Regards,
TA