Finding the MAX below a limit

Thomas,

    Sample query as given below, should get you the desired
results, showing DateIn, DateOut and Duration (in days)
against each name.

    T_Data is the name of table having fields strName &
dtDate.

Best wishes,
A.D.Tejpal
---------------

Q_RunningDateGap (Select Query)
====================================
SELECT T_Data.strName, T_Data.dtDate AS DateIn, (Select
Min(dtDate) From T_Data as T1 Where T1.strName =
T_Data.strName And  T1.dtDate > T_Data.dtDate) AS
DateOut, IIf((Select Min(dtDate) From T_Data as T1 Where
T1.strName = T_Data.strName And  T1.dtDate >
T_Data.dtDate)>=[dtDate],(Select Min(dtDate) From T_Data
as T1 Where T1.strName = T_Data.strName And  T1.dtDate >
T_Data.dtDate)-[dtDate],Null) AS Duration
FROM T_Data  
ORDER BY T_Data.strName, T_Data.dtDate;
====================================

  ----- Original Message ----- 
  From: Thomas Ewald 
  To: ACCESS-LPEACH.EASE.LSOFT.COM 
  Sent: Saturday, December 16, 2006 00:16
  Subject: Finding the MAX below a limit


  If I want to find the highest number (or, in this case,
date)  below 
  another one, how would that be expressed in SQL?

  For example, take this simplified table:

  strName (string)
  dtDate          (date)

  Each strName can be present many times in the table with
differing dates. 
  Let's use this example:

  JohnSmith 11/1/06
  JohnSmith 11/2/06
  JohnSmith 11/20/06
  JohnSmith 12/01/06

  I would like to produce the following results

  JohnSmith       11/1/06         11/2/06
  JohnSmith       11/2/06         11/20/06
  JohnSmith       11/20/06        12/01/06

  Not sure what I'd do with the first and last ones, but
that's not too 
  important. I could probably ignore them, filtering the
data using only 
  those instances without nulls. Of course, there are many
names as well as many dates in the full table.

  I would expect to use two copies of the table with
A.strName = B.strName, but am not sure what to do to find
the most recent date before (or the soonest date after)
another date. I look forward to hearing from the group's
wisdom.

  Oh, another wrinkle: In the actual application, I would
have two matching fields, not just one (like name in this
example). I would not expect that to add much complexity to
the job, but just be a matter of adding another A.x = B.x.
Am I wrong?

  TIA,

  Thomas F. Ewald
  FCS Database Manager
  General Dynamics Land Systems
  (586) 276-1256

------------------------------------------------------------
--------------
The ACCESS-L list is hosted on a Windows(R) 2000 Server
running L-Soft
international's LISTSERV(R) software.  For
subscription/signoff info
and archives, see ht
tp://peach.ease.lsoft.com/archives/access-l.html .
                             COPYRIGHT INFO:
http://peach.ease.lsoft.com/scripts/w
a.exe?SHOWTPL=COPYRIGHT&L=ACCESS-L