Recent content by soldat452002

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

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

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.

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

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;

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

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

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

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

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

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.

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] ='" &...

Thanks for your help ☺

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