Find Match by Date Range (1 Viewer)

[soldat452002]

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 please?

ProducerId Year State DateEff DateExp Status
0000338191 2016 OR 1/1/2016 1/1/2200 TEXP


tblProducer

ProducerId Year State DateEff DateExp Status
0000338191 2016 OR 1/1/2016 1/1/2200
0000338191 2015 OR 1/1/2015 12/31/2015
0000338191 2014 OR 1/1/2014 12/31/2014
0000338191 2013 OR 1/1/2013 12/31/2013
0000338191 2012 OR 1/1/2012 12/31/2012
0000338191 2011 OR 1/1/2011 12/31/2011
0000338191 2010 OR 1/1/2010 12/31/2010
0000338191 2009 OR 1/1/2009 12/31/2009
0000338191 2008 OR 1/1/2008 12/31/2008
0000338191 2007 OR 1/1/2007 12/31/2007
0000338191 2006 OR 1/1/2006 12/31/2006

TblStatus

ProducerID State Status Eff Exp
0000338191 OR TERMREL 8/7/2015 4/30/2016
0000338191 OR COMPLETE 10/3/2013 8/6/2015
0000338191 OR TEXP 5/1/2016 01/01/2200

 

[CJ_London]

not sure how the dates are supposed to relate to each other

SELECT tblProducer.*, tblStatus.Status
FROM tblProducer inner join tblStatus on tblProducer.producerid=tblStatus.ProducerID
WHERE tblProducer.DateEff between tblStatus.Eff and tblStatus.Exp

if this is not what is required, please clarify how the dates are related to work and whether the state is also relevant

the above code is saying it will

'return the status where the dateeff field in tblproducers is between the eff and exp dates in tblstatus for the same producerid'

 

[soldat452002]

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

 

[CJ_London]

you need to answer this

please clarify how the dates are related to work and whether the state is also relevant

 

[soldat452002]

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 Specific. This Table does not have Year, but rather Date Ranges.



ProducerId Year State DateEff DateExp Status
0000338191 2016 OR 1/1/2016 9/1/2016 TERMEL

ProducerID State Status Eff Exp
0000338191 OR TERMREL 8/7/2015 4/30/2016

 

[CJ_London]

your explanation is still not clear, but I think what you need is

SELECT tblProducer.*, tblStatus.Status
FROM tblProducer inner join tblStatus on tblProducer.producerid=tblStatus.ProducerID
WHERE tblProducer.DateEff <=tblStatus.Exp AND tblProducer.DateExp >=tblStatus.Eff

 

[soldat452002]

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.

 

[CJ_London]

OK - I'm tired of guessing - provide some clear example data and the clear result you want from that example data plus a written description of how the data is related between the two tables. The code I provided does what you asked I think?? so do not understand what you mean by merge tables

 

[soldat452002]

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 tblStatus.Affiliation field.

Desired Result:

tblStatus.ID Status tblStatus.Eff tblStatus.Exp Affiliation
012344 TMUTUAL 11/1/2011 6/30/2016 LLC




tblStatus.ID Status tblStatus.Eff tblStatus.Exp Affiliation
012344 COMPLETE 1/1/2000 10/31/2011 Null
012344 TMUTUAL 11/1/2011 6/30/2016 Null

TblAffiliation.ID Affiliation TblAffiliation.Eff TblAffiliation.Exp
012344 LLC 1/1/2010 4/30/2010
012344 INC 5/1/2010 05/01/2016

 

[plog]

... if the tblStatus.Eff & Exp has a Date Range found within tblAffiliation.Eff & Exp...

tblStatus.ID Status tblStatus.Eff tblStatus.Exp Affiliation
[A] 012344 COMPLETE 1/1/2000 10/31/2011 Null
012344 TMUTUAL 11/1/2011 6/30/2016 Null

TblAffiliation.ID Affiliation TblAffiliation.Eff TblAffiliation.Exp
[C] 012344 LLC 1/1/2010 4/30/2010
[D] 012344 INC 5/1/2010 05/01/2016

You description doesn't match your desired results. I added letters to denote which records I am referencing:

1. According to your definition [A] matches [C] & [D], not just [C] as you put in your example data.

2. According to your definition matches [D], but is not in your sample data.

Based on your description your desired results should include 3 records (AxC, AxD, BxD). Please write a better definition to explain why only 1 of those is in your desired results.

 

[soldat452002]

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 '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 tblStatus.Exp Affiliation
[A] 012344 COMPLETE 1/1/2000 10/31/2011 [C]
012344 TMUTUAL 11/1/2011 6/30/2016 Null [D]

TblAffiliation.ID Affiliation TblAffiliation.Eff TblAffiliation.Exp
[C] 012344 LLC 1/1/2010 4/30/2010
[D] 012344 INC 5/1/2010 05/01/2016

 

[CJ_London]

well, I've given it my best shot. Hopefully Plog will have better luck.

good luck with your project

 

[soldat452002]

Thanks for your help ☺

 

[plog]

You haven't made a logical case for what you want. For starters you need to explain why A and D aren't matched. Their dates meet the criteria, how come A&D aren't matched?

 

[soldat452002]

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] ='" & [TID] & "' And #" & [Exp] & "# Between [Eff] And [Exp]")


tblAffiliation
TID Affiliation Eff Exp
22764 BELA, INC 11/1/2008 10/2/2013
22764 INDIVIDUAL 10/3/2013 3/31/2014
22764 CONSULTING INC 4/1/2014 12/31/9999

tblStatus
TID Status Eff Exp Affiliation
22764 COMPLETE 1/1/2000 6/30/2012
22764 INACTIVE 7/1/2012 10/2/2013
22764 COMPLETE 10/3/2013 4/30/2014
22764 TMUTUAL 5/1/2014 12/31/9999

 

[plog]

Look back through CJ's posts. He constantly asks the same clarifying questions to which you vaguely respond. You are now doing the same thing with me. Your last post seems unrelated to the questions I am asking. You bringing in new sample data and code that doesn't work only makes things less clear to me.

So now we take a step back.

Here's your sample data:
tblStatus.ID Status tblStatus.Eff tblStatus.Exp Affiliation
[A] 012344 COMPLETE 1/1/2000 10/31/2011 Null
012344 TMUTUAL 11/1/2011 6/30/2016 Null

TblAffiliation.ID Affiliation TblAffiliation.Eff TblAffiliation.Exp
[C] 012344 LLC 1/1/2010 4/30/2010
[D] 012344 INC 5/1/2010 05/01/2016

What data do you expect a query to return? No explanations, just show me data of what a query should return.

 

[soldat452002]

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 INC

This is the desired result, If the Range is within, bring it over.

 

[plog]

Now its time for an explanation. Why is this not part of the desired result?

[A] 012344 COMPLETE 1/1/2000 10/31/2011 INC

 

[soldat452002]

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 4/1/2014 12/31/9999 COMPLETE [F]

tblStatus
TID Status Eff Exp Affiliation
[D] 22764 COMPLETE 1/1/2000 6/30/2012
[E] 22764 INACTIVE 7/1/2012 10/2/2013
[F] 22764 COMPLETE 10/3/2013 4/30/2014
[G] 22764 TMUTUAL 5/1/2014 12/31/9999

 

[plog]

Because when the Date Ranges are within the same year (2014), it either returns the most recent Status Code.

I have no idea what that sentence means. You need to be very explicit in declaring what tables/fields you mean.

'...the Date Ranges...' the date ranges of what table?

'...most recent Status Code...'. You have no field called [Status Code]. Nor did you define how to determine what 'most recent' is. You have 2 date fields, which one determines the order?