A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Lag 1 day relationship



 
 
Thread Tools Display Modes
  #1  
Old May 13th, 2010, 09:30 PM posted to microsoft.public.access.queries
michelle
external usenet poster
 
Posts: 818
Default Lag 1 day relationship

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.


--
Cheers,
Michelle
"Have no fear of perfection - you’ll never reach it." - Salvador Dali
  #2  
Old May 13th, 2010, 09:40 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Lag 1 day relationship

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

--
Build a little, test a little.


"Michelle" wrote:

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.


--
Cheers,
Michelle
"Have no fear of perfection - you’ll never reach it." - Salvador Dali

  #3  
Old May 13th, 2010, 10:03 PM posted to microsoft.public.access.queries
michelle
external usenet poster
 
Posts: 818
Default Lag 1 day relationship

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.

--
Cheers,
Michelle
"Have no fear of perfection - you’ll never reach it." - Salvador Dali


"KARL DEWEY" wrote:

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

--
Build a little, test a little.


"Michelle" wrote:

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.


--
Cheers,
Michelle
"Have no fear of perfection - you’ll never reach it." - Salvador Dali

  #4  
Old May 13th, 2010, 10:20 PM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default Lag 1 day relationship

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

Michelle wrote:
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.)


--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


  #5  
Old May 14th, 2010, 04:17 AM posted to microsoft.public.access.queries
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Lag 1 day relationship

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

--

John W. Vinson [MVP]
  #6  
Old May 14th, 2010, 01:49 PM posted to microsoft.public.access.queries
michelle
external usenet poster
 
Posts: 818
Default Lag 1 day relationship

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?


--
Cheers,
Michelle
"Have no fear of perfection - you’ll never reach it." - Salvador Dali


"John W. Vinson" wrote:

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

--

John W. Vinson [MVP]
.

  #7  
Old May 14th, 2010, 02:01 PM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default Lag 1 day relationship

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


  #8  
Old May 14th, 2010, 02:19 PM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default Lag 1 day relationship

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


  #9  
Old May 14th, 2010, 03:28 PM posted to microsoft.public.access.queries
michelle
external usenet poster
 
Posts: 818
Default Lag 1 day relationship

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.

--
Cheers,
Michelle
"Have no fear of perfection - you’ll never reach it." - Salvador Dali


"Bob Barrows" wrote:

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

  #10  
Old May 14th, 2010, 04:01 PM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default Lag 1 day relationship

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 wrote:
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.


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


--
HTH,
Bob Barrows


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:31 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.