Retrieve maximum value from multiple fields

E

ehunter

Hi,

I need to retrieve the maximum date from several columns but I'm not sure
how. For example, a forest cutblock has multiple stages in it's life cycle
that we track the dates of completion (as separate columns). I want to know
the most recent date.

Thanks!

Erin
 
S

strive4peace

Hi Erin,

have you considered restructuring your data so that dates are stored in
another table with one date per record?

*DateTypes*
DateTypeID, autonumber
DateType, text
IsActive, yes/no, DefaultValue=true -- value is false for historical
DateTypes

*TrackDates*
DateID, autonumber
SomeID, long integer -- FK to table that dates relate to
DateTypeID, long integer -- FK to DateTypes table
TrackDate, date/time


if you did this, what you are asking would be very easy :) And it would
also be easy to add additional DateTypes in the future as well as remove
them


Warm Regards,
Crystal
*
:) have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
E

ehunter

No, I do not have that option. I am linking to an oracle database for which
I do not have edit permissions. I need a solution within the given
circumstances.
 
J

John W. Vinson

No, I do not have that option. I am linking to an oracle database for which
I do not have edit permissions. I need a solution within the given
circumstances.
Click to expand...

You could create a UNION query selecting the fields:

SELECT IDfield, thisdate FROM oracletable WHERE thisdate is not null
UNION ALL
SELECT IDfield, thatdate FROM oracletable WHERE thatdate is not null
UNION ALL
SELECT IDfield, otherdate FROm oracletable WHERE otherdate is not null

This will "unravel" your wide-flat table structure into a (dynamic, not
stored) tall-thin structure; you can then base a TOTALS query on the saved
UNION query.

John W. Vinson [MVP]
 
Top