Pulling the oldest date from range of dates

[NewAccessDude]

Greetings,

I have been asked to upload a large report provided to us by a 3rd party and
pull very specific columns, then with that data I need the oldest date
available in the column Evt Dt.

For example:

COL1 COL2 COL3 COL4 COL5 COL6 COL7
info info info info info info
8/20/2008
info info info info info info
8/13/2008

I have pulled columns 1-7 from the massive report provided to us, but I have
duplicate information where the only difference is the date. I need to pull
the oldest date for my query.

SELECT DISTINCTROW [BNSF 2].Unit, [BNSF 2].Train, [BNSF 2].Destination,
[BNSF 2].Vessel, [BNSF 2].Voyage, [BNSF 2].[Evt Dt], Trains.Route
FROM [BNSF 2] INNER JOIN Trains ON [BNSF 2].[Destination St]=Trains.State
GROUP BY [BNSF 2].Unit, [BNSF 2].Train, [BNSF 2].Destination, [BNSF
2].Vessel, [BNSF 2].Voyage, [BNSF 2].[Evt Dt], Trains.Route
HAVING ((([BNSF 2].Train)<>""))
ORDER BY [BNSF 2].Unit, [BNSF 2].[Evt Dt];

Thanks

 

[NewAccessDude]

Actually after reading a few more posts, I tried using the MIN function on
the Evt Dt field and it seems to be pulling the oldest (does min = oldest?),
is this a viable solution?

Thanks again!

 

[Lord Kelvan]

ok here an explination

a date translates into a number

01/04/2008 = 39539
02/04/2008 = 39540

and so on

when you apply a numerical function such as max or min to a date it
treats it as its repesenting number

so in releation to it being "oldest" is only true in releation to a
period of time on a computer

so yes it is the solution you want

hope this helps

regards
kelvan

 

[Lord Kelvan]

as a note if you want to know what number releats to a date in the
query builder dont add any tables and just type

datenum: format("01/04/2008",0)

and that will output the number for you so just add whatever date you
want and you can see its number

 

[NewAccessDude]

fantastic! thanks for the information