Calculate Date Diff/Multiple Criteria and Records (1 Viewer)

ChrisMcKenzie

New member
Local time
Yesterday, 16:13
Joined
May 5, 2015
Messages
6
Hello. I am new to Access and looking for assistance with an issue. I had created a rudimentary database to track the violations of criminal offenders on a specific probation program. I was recently asked to determine the number of days between violations for each offender in the program, based on multiple criteria. I had been exporting a query to Excel and calculating the functions using a formula there, but I am rotating out of this position and need to find a way to automate the calculation for my successor. I have a query which pulls fields from three different tables (tblOffenders, tblCases, tblPTRPs) and gives me the following results (basically):

Name.........Case.....Induction..PTRP...PTRP.....Arrest...Disposition...Release
Last, First.....#............Date.......#........Date.....Date........Date..........Date
Doe, John....3-07-9...1/27/12...3....... 4/19/12... 4/16/12.. 4/20/12... 4/22/12
Doe, John....3-07-9...1/27/12...4....... 5/10/12
Doe, John....3-07-9...1/27/12...4.1.... 5/18/12... 5/16/12... 6/1/12... 5/17/12
Doe, John....3-07-9...1/27/12...5....... 6/11/12................... 6/11/12... 6/11/12
Paul, Peter...3-11-6...8/2/11
Smith, Jane..3-95-8..1/14/10....1...... 2/25/11... 2/25/11.. 2/28/11... 2/28/11
Smith, Jane..3-08-1..1/14/10....1...... 2/25/11... 2/25/11.. 2/28/11.. 2/28/11
Smith, Jane..3-95-8..1/14/10....2...... 2/24/12... 2/22/12.. ...............2/28/12
Smith, Jane..3-08-1..1/14/10....2...... 2/24/12... 2/22/12................. 2/28/12
Smith, Jane..3-95-8..1/14/10....2.1... 8/3/12..... 8/2/12................... 8/3/12
Smith, Jane..3-08-1..1/14/10....2.1... 8/3/12..... 8/2/12................... 8/3/12
Smith, Jane..3-95-8..1/14/10....2.2... 9/5/13..... 9/5/13... 2/12/14... 9/6/13
Smith, Jane..3-08-1..1/14/10....2.2... 9/5/13..... 9/5/13... 2/12/14... 9/6/13

Case # is the criminal case. A person may have multiple cases. Induction date is the date the person started this specific program. PTRP # is the violation report number. Some reports are amended later (reflected by a decimal point, e.g, PTRP #2.1 is the 1st Amended 2nd PTRP. Amended PTRPs are usually filed if a new violation is reported before the previous violation has been resolved by the court). They do not necessarily start at 1, PTRP Date is the date the violation report was filed with court. Arrest date is the date the person was arrested for the violation. Disposition Date is the date the violation was resolved by the court. Release Date is the date the person was released from jail (either after serving a sanction imposed by the court or to bail while a violation is pending disposition). Some people have no PTRP listed, which means they have not had a violation. Some have no arrest date, which means that either a warrant or summons was issued.

So, I need a column for the calculation of days between each violation based on the case number. If there is no violation, the result should be left blank. If this is the first violation, the difference needs to be between the PTRP date OR Arrest Date (which ever comes first) and the Induction date. If there is a previous violation, then the result needs to be the number of days between the Date of Release OR Disposition (whichever is later) of the previous violation in that case and the date of PTRP OR Date of Arrest in the next chronological violation (whichever is earlier). Please note that the query above is sorted chronologically by release date for person, not by case number.

I have no idea where to even start with this, so any help would be appreciated. If it matters, I am using Access 2010. The query I am using to build the above table (which contains more info than I displayed) is below:

SELECT tblOffenders.lngzOffNum, IIf([blnDischarged]=-1,"N","Y") AS Active, tblOffenders.chrOffLstNm, tblOffenders.chrOffFstNm, IIf([chrGender]="M","M","F") AS Gender, tblOffenders.dtmDOB, tblCases.dtmProbStart, tblCases.dtmIndDate, tblCases.intLSIR, tblCases.chrCaseNum, IIf([intAmndNum]<>0,[intPTRNum] & "." & [intAmndNum],[intPTRNum]) AS PTRP, tblPTRPs.dtmPTRP, tblPTRPs.lngzViolationType, tblPTRPs.lngzViolation, Switch([tblPTRPs]![blnWRqst]=-1,"Y",[tblPTRPs]![blnWRqst]=0,"N") AS Warrant, tblPTRPs.dtmArrest, tblPTRPs.lngzArrestType, tblPTRPs.dtmDispo, tblPTRPs.dtmRls, tblPTRPs.intTmActl, IIf([blnDischarged]=-1,"Y","N") AS Discharged, tblCases.lngzDCReason, tblCases.dtmDischargeDt
FROM (tblOffenders INNER JOIN tblCases ON tblOffenders.lngzOffNum = tblCases.lngzOffNum) LEFT JOIN tblPTRPs ON tblCases.lngzCaseID = tblPTRPs.lngzCaseNum
ORDER BY tblOffenders.chrOffLstNm, tblOffenders.chrOffFstNm, tblCases.chrCaseNum, tblPTRPs.dtmPTRP;
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 00:13
Joined
Feb 19, 2013
Messages
16,553
using Jane Smith as the example, can you manually calculate what you expect to see and provide that information, your detailed description is not clear to me - for example you refer to 'PTRP date OR Violation date ' but I can't see what would be the violation date
 

ChrisMcKenzie

New member
Local time
Yesterday, 16:13
Joined
May 5, 2015
Messages
6
My apologies. That was an error. It should have (and now does) read PTRP date or Arrest Date (whichever is earliest). I also forgot to state that if the previous record has no Disposition Date, then it should read 0.

Name.........Case.....Induction..PTRP...PTRP.....A rrest...Disposition...Release
Last, First.....#............Date.......#........Date... ..Date........Date..........Date
Smith, Jane..3-95-8..1/14/10....1...... 2/25/11... 2/25/11.. 2/28/11... 2/28/11: 228 days (2/25/11 - 1/14/10)
Smith, Jane..3-08-1..1/14/10....1...... 2/25/11... 2/25/11.. 2/28/11.. 2/28/11: 228 days (2/25/11 - 1/14/10)
Smith, Jane..3-95-8..1/14/10....2...... 2/24/12... 2/22/12.. ...............2/28/12: 359 days (2/22/12 - 2/28/11)
Smith, Jane..3-08-1..1/14/10....2...... 2/24/12... 2/22/12................. 2/28/12: 359 days (2/22/12 - 2/28/11)
Smith, Jane..3-95-8..1/14/10....2.1... 8/3/12..... 8/2/12................... 8/3/12: 0 days (since Disposition Date is null)
Smith, Jane..3-08-1..1/14/10....2.1... 8/3/12..... 8/2/12................... 8/3/12: 0 days (since Disposition Date is null)
Smith, Jane..3-95-8..1/14/10....2.2... 9/5/13..... 9/5/13... 2/12/14... 9/6/13: 0 (since Disposition Date is null)
Smith, Jane..3-08-1..1/14/10....2.2... 9/5/13..... 9/5/13... 2/12/14... 9/6/13: 0 (since Disposition Date is null)
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 00:13
Joined
Feb 19, 2013
Messages
16,553
OK I can see the basic principle, but you have commented '(since Disposition Date is null)' even when it isn't and have calculated values when it is - please can you clarify.

Just taking one offence

Name.........Case.....Induction..PTRP...PTRP.....A rrest...Disposition...Release
Last, First.....#............Date.......#........Date... ..Date........Date..........Date
Smith, Jane..3-95-8..1/14/10....1...... 2/25/11... 2/25/11.. 2/28/11... 2/28/11: 228 days (2/25/11 - 1/14/10)
Smith, Jane..3-95-8..1/14/10....2...... 2/24/12... 2/22/12.. ...............2/28/12: 359 days (2/22/12 - 2/28/11)
Smith, Jane..3-95-8..1/14/10....2.1... 8/3/12..... 8/2/12................... 8/3/12: 0 days (since Disposition Date is null)
Smith, Jane..3-95-8..1/14/10....2.2... 9/5/13..... 9/5/13... 2/12/14... 9/6/13: 0 (since Disposition Date is null)

line1 takes induction date from earlier of PTRP/Arrest date
line2 takes release date of line1 from earlier of PTRP/Arrest date - why?
line3 takes release date from earlier of PTRP/Arrest date? or 0 because disposition date of line3 is null? or 0 because disposition date of line2 is null?
line4 has 0 because disposition date of line3 is null? or 0 because disposition date of line2 is null?

Also, please can you clarify the PTRP number datatype - is it text or numeric (double or single)? Just need to know because they are treated differently in calculations.
 

ChrisMcKenzie

New member
Local time
Yesterday, 16:13
Joined
May 5, 2015
Messages
6
PTRP number data type is text.

The purpose is to determine the number of days between each violation (PTRP - which is an acronym for petition to revoke probation).

line1 is the first violation since the offender started the program, so the number of days is between the Induction Date in line1 and the earlier of PTRP/Arrest Date in line1.

line2 is the second violation, so I need to determine how many days the offender was free in the community between Disposition Date/Release Date (whichever is later) in line1 and Date of Arrest/PTRP Date (whichever is earlier) in line 2.

line3 is 0 because there is no Disposition Date in line2.

line4 is 0 because there is no Disposition Date in line3.

(All though technically, they are both 0 because there is no Disposition Date in line2. Since they are both amended violations from line2 as reflected by the PTRP # having the same integer part and different fractional parts)

There must be a disposition date in the previous record in the case, which indicates the offender went before a Judge and received a sanction for the violation(s). Otherwise the result will be 0.

Sometimes, the PTRP will be written prior to the offender's arrest (if an offender does not report to the PO) and sometimes the arrest will come before the PTRP (police arrest the offender for a previously unkown new crime). Likewise, Disposition Date may come before the Release Date (if the offender is released on bail and then appears before a judge while out of custody) or it may come after the Disposition Date (judge sanctions the offender to a period in jail). And sometimes a PTRP will be amended prior to the Disposition Date to add new probation violations (thus the examples above of PTRP # 2, 2.1, 2.2 - which mean PTRP #2, 1st Amended PTRP #2, and 2nd Amended PTRP #2).

Clear as mud. It gets worse when you have to keep track of all of it.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 00:13
Joined
Feb 19, 2013
Messages
16,553
bit clearer than mud:) I understand you comments, but final few of questions (hopefully) before suggesting a solution.

1. Do the records have a uniqueID? If so, is it incremental? Not sure I need it, but always good to know
2. do you actually have an 'offenderId' rather than lastname/firstname? Simplifies the coding if you have
3. Is it correct to say that name and case# and induction date will always be the same? i.e. can you have same name and case but different induction date which needs to be within the 'calculation'? - needed to determine the 'calculation set'. Within your existing data, do you have a unique ID for this? again, make the solution simpler.
4. is there any chance that a ptrp number can be repeated within the same set of data or are there measures in place to ensure this can't happen? i.e. within a set of same name/case/inductiondate records, ptrp will be unique
5. Does the ptrp no always ascend - i.e. 1,2, 2.1,2.2, 3 rather than 1,2,3,2.1,2.2. Required to determine the previous record.

6.
(All though technically, they are both 0 because there is no Disposition Date in line2. Since they are both amended violations from line2 as reflected by the PTRP # having the same integer part and different fractional parts)
so I could just use disposition date in line2 as being the 'first' record. Remember this is access, not excel.
 

ChrisMcKenzie

New member
Local time
Yesterday, 16:13
Joined
May 5, 2015
Messages
6
1. The example above was built by a query from three different tables. Each record does not have a unique identifier. There is a unique identifier for each offender (OffNum), a unique identifier for each case number (CaseID) and a unique identifier for each PTRP # (PTRNum) on the underlying tables (tblOffenders, tblCases and tblPTRPs. tblCases is joined to tblOffenders and tblPTRPs is joined to tblCases).
2. There is a unique ID (OffNum) for each offender.
3. It is correct to the state that the same offender and the same case number will always have the same induction date. An offender may have multiple cases, each with a different induction date, but the Induction Date field is taken from the Cases table.
4. In the tblPTRPs, there are two fields: PTRP # and Amended #. The PTRP # in the query that I am using is a concatenation of those two fields with a decimal point between them. So, in the underlying table, the PTRP # can be repeated. In the query it does not.
5. In the query the PTRP # will always ascend.
6. Yes, I suppose you could use line2 in the example as being the first record. :) This is my first real foray into Access, but it made more sense to track this program this way rather than using Excel.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 00:13
Joined
Feb 19, 2013
Messages
16,553
thanks Chris,

I'm very busy right now but will take a look this pm
 

CJ_London

Super Moderator
Staff member
Local time
Today, 00:13
Joined
Feb 19, 2013
Messages
16,553
I've attached the table and query since I think this will be easier for you to follow

Since your 'source' is a query based on three other tables, it is not as efficient as it might be, you will probably be better to incorporate it into the query that generates what I have called Qtbl. Ideally, do your calculations as early as possible and carry the result through

I do come out will a different result for line 1 although I am using the same figures - you have

228 days (2/25/11 - 1/14/10)

and I get 407

since 25th Feb is later than 14th Jan, I think 407 is correct

Anyway, see what you think
 

Attachments

  • PTRP.accdb
    696 KB · Views: 120

Users who are viewing this thread

Top Bottom