Oops, I forgot the ID relationship in that saved query (although it
might not make any difference ...)

Select T1.ID,T1.date,
(select max(T2.date) from T2 WHERE T1.ID=T2.ID
AND T2.date T1.date)
As T2date
FROM T1


Bob Barrows wrote:
It seems that T2 is essentially your holiday table. It's not going to
perform well, but a correlated subquery will do the trick. Create this
saved query (call it Q1):

Select T1.ID,T1.date,
(select max(T2.date) from T2 WHERE T2.date T1.date)
As T2date
FROM T1


Now you can join Q1 to T2:

SELECT Q1.ID, Q2.date,T2.whatever
FROM Q1 JOIN T2 ON Q1.ID=T2.ID AND Q1.T2date=T2.date


Michelle wrote:
I was hoping there was some subquery I could run to look for the next
highest day and populate date2 with that. I'm not looking for
software to be telepathic. That would be ridiculous.

When I need a 1 day lag when I pull data just for one day I simply
use the same end date as before and then query that date -5 days (in
case there is a weekend and holiday in between dates). Then I use
another query that contains the following subquery to calculate the
2nd highest date...

SELECT Max(AS_OF_DATE) AS 2ndMax INTO 2ndDate_Tbl
FROM 2ndMaxRaw
WHERE (((2ndMaxRaw.AS_OF_DATE)(SELECT Max(AS_OF_DATE)
FROM InfoTbl)));

Then I use the resulting date to query in the table where I need a 1
day lag. When it's just 1 day I can ignore the actual date since I
know everything queried is for the same date. The problem with using
this method on a date range is I need to relate the 2 dates properly.
I was hoping to use the date2 to join the tables. This way you can
use Access to keep track of your weekends and holidays indirectly.
Make sense?



On Thu, 13 May 2010 13:30:01 -0700, Michelle
wrote:

I also don't want to have
to create a holiday table since it is one more thing that would
have to be maintained. (Meaning it would get forgotten.)

Sorry, Michell, if you want Access to keep track of the holidays
that *your business* observes, you will have to somehow tell Access
what those holidays are.

The telepathic interface won't be out until Access 2021 (same
release as the time machine interface,which is how I knew...) bg

--
HTH,
Bob Barrows