ChrisMcKenzie
New member
- Local time
- Today, 04:22
- 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;
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: