Lag 1 day relationship

[Michelle]

Sorry if this is a duplicate, but I tried lots of searches here. I can't
seem to find my answer anywhere.

I have 2 tables that I need to join by date and ID. The problem is that
there is a day lag in one of the tables, i.e. date = 5/12/10 in T1 is
equivalent to date = 5/11/10 in T2. I cannot change the structure of either
of these tables, so please do not suggest that. I need to be sure I'm
comparing the proper dates between tables. I can't simply do a dateadd -1
since there are holidays and weekends to consider. 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.)

I can do this if I only pull 1 date, but I have to pull MTD data, i.e. a
range. My current technique won't easily work for a range. Is there a way
to create a date2 column in T1 so I can query the same range in both tables,
but date2 would contain the -1 business day that I could then use to join
the 2 tables? I was thinking there might a way to look for the next lower
date using the date column as a base. Hopefully this all makes sense.
Here's my data structure, and my proposed date2 column...

T1
date ID date2
5/12/10 1 5/11/10
5/12/10 2 5/11/10
5/11/10 1 5/10/10
5/11/10 2 5/10/10
5/10/10 1 5/7/10
5/10/10 2 5/7/10
5/7/10 1 5/6/10
5/7/10 2 5/6/10

T2
date ID
5/11/10 1
5/11/10 2
5/10/10 1
5/10/10 2
5/7/10 1
5/7/10 2
5/6/10 1
5/6/10 2

This way I could join using date in T2 and date2 in T1. Any help is greatly
appreciated. I'm totally stuck.

 

[KARL DEWEY]

Easy! Create a select query adding a calculted field to add the one day and
use the query instead of T2.

 

[Michelle]

How does this account for if the -1 date being a weekend or holiday? If I
understand you correctly 5/10/10 would become 5/9/10, which is a Sunday. The
join won't work properly. I really want 5/10/10 to go to 5/7/10. That way I
can join the tables properly.

 

[Bob Barrows]

There is no way to do this without a holiday table. Sorry.
Calendar tables are extremely useful things. You can find some useful
information here:
http://www.mvps.org/access/datetime/index.html

 

[John W. Vinson]

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>

 

[Michelle]

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?

 

[Bob Barrows]

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

 

[Bob Barrows]

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

 

[Michelle]

Bob,

This is exactly what I was looking for. Yes, T2 is essentially my holiday
table. I wasn't thinking of it that way before. Thank you kindly for your
help.


John, I really didn't appreciate your prior sarcastic comments. It wasn't
at all necessary.

 

[Bob Barrows]

If you stick around and read more of the posts, I think you'll realize
that John was not trying to be nasty or sarcastic. "Gently chiding" at
the worst, but the intent was to make you smile and prompt you for
further information. Intentions are very difficult to convey in
plain-text messages like this so please try not to jump to conclusions
so quickly.

And his post did serve the purpose of getting you to further clarify
things which allowed me to realize that T2 was your holiday table, and
allowed you to get your solution.

 

[Michelle]

There are better ways of getting more information out of people. As you
aptly point out, sarcasm doesn't carry well over text. I would have
explained myself further without the sarcasm. I still don't think it was
necessary or useful.

 

[John W. Vinson]

John, I really didn't appreciate your prior sarcastic comments. It wasn't
at all necessary.

I apologize. Sometimes my sense of humor is inappropriate.