Lag 1 day relationship
in [Queries]
|
Prev: First function returning min
Next: Friday's Due Date
From: Michelle on 13 May 2010 16:30 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
From: KARL DEWEY on 13 May 2010 16:40 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
From: Michelle on 13 May 2010 17:03 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
From: Bob Barrows on 13 May 2010 17:20 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 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"
From: John W. Vinson on 13 May 2010 23:17
On Thu, 13 May 2010 13:30:01 -0700, Michelle <Michelle(a)discussions.microsoft.com> 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] |