Query to Find Gaps in Dates (2 Viewers)

[soldat452002]

Hello Guys,

I have an access database that we use for billing.

The issue I am having is finding gaps in dates so we don't bill for that period.
So if I had a lapse within the Contract Eff Date 8/1/2007 and the Last End Date 12/31/2016 I would need to pull that data. Can anyone help me please. I tried everything so far. Thanks:banghead:


ID Year ContractEff EffEndDate
AA001 2007 8/1/2007 12/31/2007
AA001 2008 1/1/2008 12/31/2008
AA001 2009 1/1/2009 12/31/2009
AA001 2010 1/1/2010 12/31/2010
AA001 2011 1/1/2011 12/31/2011
AA001 2012 1/1/2012 12/31/2012
AA001 2013 1/1/2013 12/31/2013
AA001 2014 1/1/2014 12/31/2014
AA001 2015 1/1/2015 12/31/2015
AA001 2016 1/1/2016 12/31/2016

 

[arnelgp]

where Month([ContractEff]) <> 1

 

[plog]

You need to provide better sample data to demonstrate your issue. Please provide 2 sets of data:

A. Starting sample data from your table(s). Provide field and table names and enough data to cover all cases.

B. Expected results based on A. Show what data you expect your final query to return based on the starting data in A.

 

[soldat452002]

tblContract.Eff and tblContract.Exp are the date fields

A Contract might Eff: 1/1/2015 and Exp: 12/31/2015
The Next Contract is Eff: 2/1/2016 to Exp: 12/31/2016
For the Same ABSID A001, I would need to pull those with a Lapse between 1/1/2015 and 12/31/2016.

 

[plog]

You provided no data in your last post. 2 sets of data, A & B. No words, just data.

 

[arnelgp]

is this what you want, qryGapsInDate.

 

[soldat452002]

Here you go, The database has a unique ID, State, Eff, Exp, Contract Type.

 

[plog]

Here, who goes? Was that for me or arnel?

If me, well, that's starting data (A). Now give me expected results (B).

 

[soldat452002]

I attached a screenshot of the starting data.

The result I would need which I cannot create is something like this:

State ABSID Year Contract Eff Exp
CA AA007 2007 FB 7/1/2007 11/23/2015

Something like this to modify AA007's 2007 Contract until 11/23/2015, The reason for this we cannot have lapses when we version our records.

 

[Galaxiom]

Your data appears to be denormalized by storing both the year and the dates. Couldn't the year be calculated by Year(ContractEff)? Also Year is also a reserved word and should not be used as a field name. State is best avoided too as it is also a reserved word in some contexts.

Use a self join. This query will show which IDs don't have a contract that starts the day after one finishes. Air code. I didn't have time to check it properly but it should get you going.

SELECT B.State, B.ABSID, B.ContractEff. B.EffEndDate
 FROM tablename AS A
 RIGHT JOIN tablename AS B
 ON A.ABSID = B.ABSID
 AND A.ContractEff = B.EffEndDate + 1
 WHERE A.ID Is Null

You may then need to remove the last contracts from that list.

BTW Do try to be consistent in you field names throughout your thread.

 

[plog]

Now, in your last post you provided 2 sets of data for expected results. I'm lost. Let's try again from the start:


Please provide 2 sets of data:

A. Starting sample data from your table(s). Provide field and table names and enough data to cover all cases.

B. Expected results based on A. Show what data you expect your final query to return based on the starting data in A.

 

[arnelgp]

as i can see it, you don't need a query but a udf/sub to fix the exp field.
see Module1, subCorrectExpField sub.
run the sub to fix the exp field.

 

[soldat452002]

I think the issue I'm having is finding lapses.

My qryLapse, AA018 has a lapse between 1/1/2014- 8/19/2014, I would need a query to find those and create a new table. Sorry I was not been clear enough.

ABSID Year Contract Eff Exp
AA018 2008 M06 1/1/2008 1/31/2008
AA018 2009 PRDCR 1/1/2009 12/31/2009
AA018 2010 PRDCR 1/1/2010 12/31/2010
AA018 2011 PRDCR 1/1/2011 12/31/2011
AA018 2012 PRDCR 1/1/2012 12/31/2012
AA018 2013 PRDCR 1/1/2013 12/31/2013
AA018 2014 SA 8/20/2014 12/31/2014
AA018 2014 PRDCR 1/1/2014 8/19/2014
AA018 2015 SA 1/1/2015 12/31/2015
AA018 2016 SA 1/1/2016 12/31/2016

 

[The_Doc_Man]

This sort of thing is better managed by a function or subroutine. I would also hope that you never have coverage overlaps.

1. Add a field to your table such as CONTRACTGAP. It can be days if you wish.

1. Write a query something similar to

SELECT ABSID, CONTYEAR, CONTRACT, EFFDATE, EXPDATE, CONTRACTGAP FROM mytable ORDER BY ABSID, CONTRACT, EFFDATE ;

(Note that Exp is the name of a function so is also a reserved word, ... )

2. Using the above query as recoded for your environment, write a VBA function that does the following:

a. Open a recordset of that query. Reset a variable that shows the current ABSID, CONTRACT, and EXPDATE
b. For each record, if it is the first record for that ABSID and CONTRACT, perhaps do nothing except reset the stored ABSID and CONTRACT and store the new ENDDATE. Set the CONTRACTGAP to something convenient, like maybe 0.
c. For each record after the first one for the current ABSID and CONTRACT, compute the date difference in days between the current record's EFFDATE and the stored EXPDATE. Perform a recordset.Edit, update the CONTRACTGAP field with that difference. Perform the recordset.Update.
d. Continue the loop until you reach the end of the recordset (recordset.EOF=True)

Now, you can run a report or query that looks for cases where CONTRACTGAP is not 1.

You COULD do this with a query, but it becomes convoluted in the extreme for those cases where you have a change of ABSID or CONTRACT value.

 

[soldat452002]

Sorry I'm lost, So if I wanted to update my table to show no lapses, how can I achieve this?

 

[arnelgp]

on your previous post you showed the records with gap.
now post (in excel) the wanna-be records you want to add in-between including the records with gap.