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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|