Search results

  1. S

    Find Overlapps in dates

    Hello So currently im working in migrating data from two Regions (East, West) same data fields but the date ranges (Eff, Exp) either could overlap or not. Im trying to build a query to identify overlapping dates and insert them in order (Oldest to Newest date). Any help would be appreciated it...
  2. S

    DateAdd to find Expiration Date.

    I see your point, Its poor table design. That's the reason I had to run a query to assign Expiration dates based on Order By, Criteria, etc. I think that's the reason that when a run my query, the date expiration dates are not sequenced. See my example database. It begins to fail after a couple...
  3. S

    DateAdd to find Expiration Date.

    There is a New Contract (M06, PRDCR, SA, etc) this determines that it is a new contract on the next row. If it were the same, it would be considered the same one.
  4. S

    DateAdd to find Expiration Date.

    This Vendor (300259) has 3 different Contract (M06, PRDCR, SA) with 3 different Effective dates (1/1/2006, 2/1/2007, 4/29/2014), each of those should expire when the other Contract begins so the Exp Date are (1/31/2007, 4,28,2014, end of time). If the Contract is the same in another row, always...
  5. S

    DateAdd to find Expiration Date.

    This is the query I been using: SELECT ID, Vendor, Eff AS BeginDate, DateAdd("d",-1,DLookUp("Eff","tblPayment","ID = " & Nz(DMin("ID","tblPayment","Vendor = " & [Vendor] & " And ID > " & [ID]),0))) AS EndDate, ID FROM tblPayment ORDER BY ID;
  6. S

    DateAdd to find Expiration Date.

    Sure, I attached a Database with my table and a Desired Result table. Thanks for helping me on this problem.
  7. S

    DateAdd to find Expiration Date.

    Im running a query to find the Min Eff Date per Vendor, State and Contract, Then I need to do a DateAdd-1 for the Expiration Date. I will update the existing Exp field. Vendor State Contract Eff Exp 300324 AZ M06 1/1/2006 1/31/2007 300324 AZ M06 1/1/2007 12/31/9999 300324 AZ PRDCR 1/1/2008...
  8. S

    DateAdd to find Expiration Date.

    Actually the desired end results will be: Vendor State Contract NewEff NewExp 300324 AZ M06 1/1/2006 12/31/2007 300324 AZ PRDCR 1/1/2008 12/31/9999
  9. S

    DateAdd to find Expiration Date.

    Hi, I have a question. How can I achieve this on a query, If the "Vendor", "State" and "Contract" are the same between multiple rows, Return the Minimum "Eff" per Contract and give it an expiration date. If there is another "Contract", then the Expiration Date has to be a Date prior (See...
  10. S

    Find Match by Date Range

    Because when the Date Ranges are within the same year (2014), it either returns the most recent Status Code. tblAffiliation TID Affiliation Eff Exp Status [A] 22764 BELA, INC 11/1/2008 10/2/2013 INACTIVE [E] 22764 INDIVIDUAL 10/3/2013 3/31/2014 COMPLETE [F] [C] 22764 CONSULTING INC...
  11. S

    Find Match by Date Range

    What data do you expect a query to return? No explanations, just show me data of what a query should return. [A] 012344 COMPLETE 1/1/2000 10/31/2011 LLC 012344 TMUTUAL 11/1/2011 6/30/2016 [B]INC This is the desired result, If the Range is within, bring it over.
  12. S

    Find Match by Date Range

    Hi, I'm trying to bring over the Affiliation to my table tblStatus by Date Range. Two Affiliations fall under the 2013 (10/1/2013-10/2/2013) and (10/3/2013-12/31/2013) How would achieve this? My Code to bring Affiliation: Affiliation: DLookUp("[Affiliation]","[tblAffiliation]","[TID] ='" &...
  13. S

    Find Match by Date Range

    Thanks for your help ☺
  14. S

    Find Match by Date Range

    Okay sorry about that, So I want a query to look at [A]'s Date Range "1/1/2000-10/31/2011" And Return [C]'s Affiliation "LLC" and for [B]'s Date Range "5/1/2010-05/01/2016 Returned [D]'s Affiliation "INC". I have to do this for thousands of records. tblStatus.ID Status tblStatus.Eff...
  15. S

    Find Match by Date Range

    Yes Is kind of complicated so here is the What I'm trying to Achieve: When I run the Query, I want to Inner Join two tables by ID. The Criteria would be that if the tblStatus.Eff & Exp has a Date Range found within tblAffiliation.Eff & Exp then bring over the Affiliation to the...
  16. S

    Find Match by Date Range

    That works, i guess my question was what is the best way to merge records from two separate tables by using date ranges as criteria.
  17. S

    Find Match by Date Range

    please clarify how the dates are related to work and whether the state is also relevant The Dates Ranges on table tblProducer is the Record per Year,State per Producer. The Status is pulled from another table tblStatus which determines the eligibility per Date Range, which is also State...
  18. S

    Find Match by Date Range

    That is wonderful, There is one issue I have encountered. How do I pull more than 1 Status if I have more than within the same year (2015). ProducerID State Status Eff Exp 000062542 CA COMPLETE 8/4/2015 7/18/2016 000062542 CA TERMREL 7/31/2015 8/3/2015
  19. S

    Find Match by Date Range

    Hello, I have a problem keeping up all night, I have to find the Status of each Producer ID found on the tblStatus per Date Range. So the results I am looking for is I'm able bring over the Status from my TblStatus.Status to tblProducer.Status by Date Range criteria. Can anyone help with this...
Top Bottom