asked on

Simple MS Access sub query doesn't seem to work

Hello,

Im trying to count the number of cases where all charges were dismissed in an MS Access (2007) database. I figured a sub query was the way to go to get cases where there is no guilty judgment and all charges were dismissed. The problem with this query is that the NOT EXISTS sub query doesn't seem to do anything as I get the same number of records with or without it. My line of thinking is for the sub query to get all the cases with convictions and then "subtract" those from the cases with "DISMISSED"

I tried with the NOT IN instead of EXISTS and the query hung up until I stopped it.

Any help you can give would be appreciated.

Heres the query and a sample of data. It should return only 2012CF000185       1 as that case has all charges dropped and no 'guilty'

SELECT DISTINCT tCharge.caseNo, tCharge.countyNo, tCharge.ID
FROM tCharge
WHERE (((tCharge.caseNo) Like "*CF*") AND (Not (tCharge.ID)=Exists (SELECT DISTINCT tCharge.ID FROM tCharge WHERE (((tCharge.caseNo) Like "*CF*") AND ((tCharge.Jdescr)="Found Guilty at Court Trial" Or (tCharge.Jdescr)="Found Guilty at Jury Trial" Or (tCharge.Jdescr)="Guilty - No Additional Sentence" Or (tCharge.Jdescr)="Guilty Due to Alford Plea" Or (tCharge.Jdescr)="Guilty Due to Guilty Plea" Or (tCharge.Jdescr)="Guilty Due to No Contest Plea") AND ((tCharge.sevClsCodeDescr) Like "fel*"));)) AND ((tCharge.Jdescr) Like "Dismissed*"))
ORDER BY tCharge.ID;

caseNo      countyNo      ID        Jdescr        sevClsCodeDescr
2012CF000186       1      2012CF000186-1      Extradited      Felony U
2012CF000185       1      2012CF000185-1      Information Filed      Felony I
2012CF000185       1      2012CF000185-1      Dismissed      Felony I
2012CF000184       1      2012CF000184-1      Information Filed      Felony H
2012CF000184       1      2012CF000184-1      Guilty Due to No Contest Plea      Felony H
2012CF000184       1      2012CF000184-1      Information Filed      Felony H
2012CF000184       1      2012CF000184-1      Dismissed on Prosecutors Motion      Felony H
2012CF000184       1      2012CF000184-1      Information Filed      Misd. U
2012CF000184       1      2012CF000184-1      Dismissed on Prosecutors Motion      Misd. U
2012CF000183       1      2012CF000183-1      Information Filed      Felony I
2012CF000183       1      2012CF000183-1      Guilty Due to No Contest Plea      Felony I
2012CF000183       1      2012CF000183-1      Information Filed      Misd. U
2012CF000183       1      2012CF000183-1      Charge Dismissed but Read In      Misd. U
2012CF000182       1      2012CF000182-1      Information Filed      Felony F
2012CF000182       1      2012CF000182-1      Amend      Felony F
2012CF000182       1      2012CF000182-1      Guilty Due to No Contest Plea      Felony I
2012CF000182       1      2012CF000182-1      Information Filed      Felony H
2012CF000182       1      2012CF000182-1      Charge Dismissed but Read In      Felony H
2012CF000181       1      2012CF000181-1      Information Filed      Felony I
2012CF000181       1      2012CF000181-1      Guilty Due to No Contest Plea      Felony I
2012CF000181       1      2012CF000181-1      Information Filed      Felony I
2012CF000181       1      2012CF000181-1      Guilty Due to No Contest Plea      Felony I
2012CF000181       1      2012CF000181-1      Information Filed      Misd. B
2012CF000181       1      2012CF000181-1      Charge Dismissed but Read In      Misd. B
2012CF000180       1      2012CF000180-1      Amended Complaint Filed      Felony H

Dale Fye

Try this. I've used a subquery with a LeftJoin rather than an Exists clause:

SELECT DISTINCT tCharge.caseNo, tCharge.countyNo, tCharge.ID
FROM tCharge
LEFT JOIN (
SELECT CaseNo FROM tCharge as T
WHERE (T.caseNo Like "*CF*") AND
((T.Jdescr = "Found Guilty at Court Trial") Or
(T.Jdescr = "Found Guilty at Jury Trial") Or
(T.Jdescr = "Guilty - No Additional Sentence") Or
(T.Jdescr = "Guilty Due to Alford Plea") Or
(T.Jdescr = "Guilty Due to Guilty Plea") Or
(T.Jdescr = "Guilty Due to No Contest Plea")) AND
(T.sevClsCodeDescr Like "fel*")
) as subQ
ON tCharge.CaseNo = subQ.CaseNo
WHERE subQ.CaseNo IS NULL

I would make sure that the subQuery is generating the set of records you want first, then add in the rest of the query.

zipnotic

ASKER

Thanks for your thought!

Giving it a try it has returned 8 times more records than expected. Hmm, am I thinking about it backwards? I want all cases with related charges that show "DISMISSED*" but have no GUILTY*, READ IN*, or AMEND*. There may be a couple of other dispositions as well.

zipnotic

ASKER

Here's the results of the left join query:

caseNo        countyNo      ID        Jdescr
2012CF001213       13      2012CF001213-13      Charge Dismissed but Read In
2012CF001213       13      2012CF001213-13      Deferred Prosecution or Sentence
2012CF001213       30      2012CF001213-30
2012CF001213       30      2012CF001213-30      Information Filed
2012CF001213       40      2012CF001213-40      Guilty Due to Guilty Plea
2012CF001213       40      2012CF001213-40      Information Filed
2012CF001213       5      2012CF001213-5      Amend
2012CF001213       5      2012CF001213-5      Charge Dismissed but Read In
2012CF001213       5      2012CF001213-5      Guilty Due to No Contest Plea
2012CF001213       51      2012CF001213-51      Amend
2012CF001213       51      2012CF001213-51      Guilty Due to No Contest Plea
2012CF001213       53      2012CF001213-53      Dismissed on Prosecutors Motion
2012CF001213       53      2012CF001213-53      Information Filed
2012CF001213       67      2012CF001213-67      Amend
2012CF001213       67      2012CF001213-67      Guilty Due to No Contest Plea
2012CF001317       13      2012CF001317-13      Deferred Prosecution or Sentence
2012CF001317       13      2012CF001317-13      Dismissed on Prosecutors Motion
2012CF001317       30      2012CF001317-30      Guilty Due to Guilty Plea
2012CF001317       30      2012CF001317-30      Information Filed
2012CF001317       40      2012CF001317-40      Dismissed on Prosecutors Motion

Dale Fye

The challenge, based on the data you have provided is that there appear to be multiple charges for a single CaseNo, so the query (as written) will only return the cases where none of the charges are among the various Guilty Options.

So, to clarify, you are looking for those cases where none of the charges have beed found Guilty. Is that correct? keep in mind that you also have a criteria in there for the ClsCodeDesc like "Fel*", which means that anyone with a Guilty determination for a misdemeanor would show up in your results. The first thing I would do is change the SELECT clause of the subQuery to SELECT DISTINCT.

SELECT DISTINCT tCharge.caseNo, tCharge.countyNo, tCharge.ID
FROM tCharge
LEFT JOIN (
SELECT DISTINCT CaseNo FROM tCharge as T
WHERE (T.caseNo Like "*CF*") AND
((T.Jdescr = "Found Guilty at Court Trial") Or
(T.Jdescr = "Found Guilty at Jury Trial") Or
(T.Jdescr = "Guilty - No Additional Sentence") Or
(T.Jdescr = "Guilty Due to Alford Plea") Or
(T.Jdescr = "Guilty Due to Guilty Plea") Or
(T.Jdescr = "Guilty Due to No Contest Plea")) AND
(T.sevClsCodeDescr Like "fel*")
) as subQ
ON tCharge.CaseNo = subQ.CaseNo
WHERE subQ.CaseNo IS NULL

If that still returns more records than you expect, I would expand the outer SELECT clause to include the fields [Jdescr] and [sevClsCodeDescr], this might help you determine why records that you don't expect are actually showing up (there may be other Felony Guilty descriptions).

Dale Fye

Are these three records related to the same case?

2012CF001213 40 2012CF001213-40 Guilty Due to Guilty Plea
2012CF001213 51 2012CF001213-51 Amend
2012CF001213 53 2012CF001213-53 Dismissed on Prosecutors

Or do you need both the CaseNo and CountyID to identify a unique case?

IF you need both of those, the you would need to modify the subquery to include both the CaseNo and CountyID columns, and then would need to modify the LEFT JOIN to include both of those. The new syntax would look like:

SELECT DISTINCT tCharge.caseNo, tCharge.countyNo, tCharge.ID
FROM tCharge
LEFT JOIN (
SELECT DISTINCT CaseNo, CountyID FROM tCharge as T
WHERE (T.caseNo Like "*CF*") AND
((T.Jdescr = "Found Guilty at Court Trial") Or
(T.Jdescr = "Found Guilty at Jury Trial") Or
(T.Jdescr = "Guilty - No Additional Sentence") Or
(T.Jdescr = "Guilty Due to Alford Plea") Or
(T.Jdescr = "Guilty Due to Guilty Plea") Or
(T.Jdescr = "Guilty Due to No Contest Plea")) AND
(T.sevClsCodeDescr Like "fel*")
) as subQ
ON tCharge.CaseNo = subQ.CaseNo AND tCharge.CountyID = subQ.CountyID
WHERE subQ.CaseNo IS NULL

zipnotic

ASKER

The ID field is contrived of the Case Number + "-" + County Number so these 3 records represent separate cases.

2012CF001213 40 2012CF001213-40 Guilty Due to Guilty Plea
2012CF001213 51 2012CF001213-51 Amend
2012CF001213 53 2012CF001213-53 Dismissed on Prosecutors

Each case (2012CFXXXXXX) can have multiple charges that are each handled separately. I'm looking to find all cases that started out as a felony (denoted by CF in case number) but ended up with all charges being dismissed. I want to exclude cases where any of the charges resulted in a conviction or any one of about 20 other dispositions. I'll attach an excel sheet of the full table if that would help you to help me. I really appreciate this. Been trying to crunch this one for awhile!

Dale Fye

Try the most recent query I sent you.

a sample spreadsheet would be helpful.

zipnotic

ASKER

I attached a sample file with 100 records. there are 300,000+ in the actual table
Sample-Data.txt

zipnotic

ASKER

I started to focus in on the chargeNo column but discounted it. That last query gave me an error that it couldn't be displayed on the grid and it asked for countID when I ran it.

SELECT DISTINCT tCharge.caseNo, tCharge.countyNo, tCharge.ID
FROM tCharge
LEFT JOIN (
SELECT DISTINCT CaseNo, CountyID FROM tCharge as T
WHERE (T.caseNo Like "*CF*") AND
((T.Jdescr = "Found Guilty at Court Trial") Or
(T.Jdescr = "Found Guilty at Jury Trial") Or
(T.Jdescr = "Guilty - No Additional Sentence") Or
(T.Jdescr = "Guilty Due to Alford Plea") Or
(T.Jdescr = "Guilty Due to Guilty Plea") Or
(T.Jdescr = "Guilty Due to No Contest Plea")) AND
(T.sevClsCodeDescr Like "fel*")
) as subQ
ON tCharge.CaseNo = subQ.CaseNo AND tCharge.CountyID = subQ.CountyID
WHERE subQ.CaseNo IS NULL

Dale Fye

In the sample data you sent me, there are only 10 Cases (CaseNo/CountyNo combinations) which meet the criteria of my subquery. These 10 cases relate to 58 of the lines of data you provided.

CaseNo        CountyNo
2012CF000167      1
2012CF000173      1
2012CF000174      1
2012CF000175      1
2012CF000176      1
2012CF000181      1
2012CF000182      1
2012CF000183      1
2012CF000184      1
2012CF000185      1

Although the remaining 42 lines contain many "Felony" ClsCodeDescr values, non of them have jDescr indicating any of the Guilty codes you provided me. There are however a number of records which indicate "Misd ..." and "Guilty".

I had to modify the query slightly to coincide with the CountyNo field you provided in the text file, and I changed the name of the table, so you will need to modify that as well. See below:

SELECT tbl_CourtCase.CaseNo, tbl_CourtCase.CountyNo, tbl_CourtCase.jDescr, tbl_CourtCase.sevClsCodeDescr
FROM tbl_CourtCase
LEFT JOIN (
SELECT DISTINCT T.CaseNo, T.CountyNo, 1 as ID
FROM tbl_CourtCase as T
WHERE (T.caseNo Like "*CF*") AND
((T.Jdescr = "Found Guilty at Court Trial") Or
(T.Jdescr = "Found Guilty at Jury Trial") Or
(T.Jdescr = "Guilty - No Additional Sentence") Or
(T.Jdescr = "Guilty Due to Alford Plea") Or
(T.Jdescr = "Guilty Due to Guilty Plea") Or
(T.Jdescr = "Guilty Due to No Contest Plea")) AND
(T.sevClsCodeDescr Like "fel*")
) AS subQ ON (tbl_CourtCase.CountyNo = subQ.CountyNo) AND (tbl_CourtCase.CaseNo = subQ.CaseNo)
WHERE ((subQ.ID Is Null))
ORDER BY tbl_CourtCase.CaseNo, tbl_CourtCase.CountyNo, tbl_CourtCase.ChargeNo;

zipnotic

ASKER

In the File I attached I'm hoping to develop a query that only returns the 1 ID, case number and county number:

2012CF000178-1, 2012CF000178, 1

Even though there are 3 records/charges associated with the case. All the rest of the cases listed have at least 1 charge with other dispositions. I'll put the results into a table and run a group by query to tally up the data statewide. I'll also do this for misdemeanor cases denoted by CM.

zipnotic

ASKER

This row would disqualify 2012CF000167 because at least 1 of the charges involved in that case is a guilty verdict.

2012CF000167 1 True 1 TRUE 946.49(1)(b) Felony H Bail Jumping-Felony 11/14/2012 6:00 TRUE NC No Contest 1/9/2013 6:00 TRUE 7 TRUE 12:00:00 AM FALSE [Guilty Due to No Contest Plea] [ 2 Years 6 Months] [Probation, Sent Withheld ] 2012CF000167-1 1 7 2012CF000167 1 Charles Pollex A. Guilty Due to No Contest Plea GNC 1/9/2013 6:00 1 1/9/2013 6:00 2 Years 6 Months 0 0

This is the trouble I've been having. I've even tried to code a solution in vba to loop thru the records.

Dale Fye

OK, lets focus on the 2012CF000166-1 case.

Based on your original criteria, there are only 7 records relating to that case. Two of those relate to "Felony H" clsCodeDescr, but one of those is Charge Dismissed the other is simply "Information Filed". What is it about this case that makes you want to exclude it from your results? Does it have to do with the "Guilty Due to No Contest Plea" records associated with "Misd. A" and "Misd. B" ClsCodeDescr?

zipnotic

ASKER

Yes, for 166-1 it is the Guilty Due to No Contest Plea for charges 2& 3.

There are 4 charges related to that case so if any 1 of those is guilty then that case should be EXCLUDed from the results.

zipnotic

ASKER

Note:

Each charge within each CASE will likely have multiple rows. The charges often start out as information filed and may be amended then end up with guilty or dismissed or deferred (amongst many other possibilities)

Dale Fye

Yes, but in the case of the 166-1 case, the Guilty is related ot misdemeanor charges, not the Felony charges. So, I think you are telling me that you want only want to see those records where none of the charges (Felony or Misdemeanor) are adjudicated as Guilty.

If that is the case, then try:

SELECT tbl_CourtCase.CaseNo, tbl_CourtCase.CountyNo, tbl_CourtCase.jDescr, tbl_CourtCase.sevClsCodeDescr
FROM tbl_CourtCase
LEFT JOIN (
SELECT DISTINCT T.CaseNo, T.CountyNo, 1 as ID
FROM tbl_CourtCase as T
WHERE (T.caseNo Like "*CF*") AND
((T.Jdescr = "Found Guilty at Court Trial") Or
(T.Jdescr = "Found Guilty at Jury Trial") Or
(T.Jdescr = "Guilty - No Additional Sentence") Or
(T.Jdescr = "Guilty Due to Alford Plea") Or
(T.Jdescr = "Guilty Due to Guilty Plea") Or
(T.Jdescr = "Guilty Due to No Contest Plea")) AND
(T.sevClsCodeDescr Like "fel*")
) AS subQ ON (tbl_CourtCase.CountyNo = subQ.CountyNo) AND (tbl_CourtCase.CaseNo = subQ.CaseNo)
WHERE ((subQ.ID Is Null))
ORDER BY tbl_CourtCase.CaseNo, tbl_CourtCase.CountyNo, tbl_CourtCase.ChargeNo;

This query removes the final portion of WHERE clause in the subQuery. In my system, it returns cases 171-1, 178-1, 180-1, and 186-1.

zipnotic

ASKER

Ah, there's maybe our disconnect. I am looking at Felony Cases instead of felony charges. The CF denotes a felony case where at least 1 of the charges was a felony. Misdemeanor charges may also by issued under that case. Felonys could also be amended to mis. So what I'm looking to derive is in CF cases (Felony cases) is there any charge, misdemeanor OR felony that results in a conviction.

I'll try your query.

Dale Fye

Oops, forgot to remove that line.

SELECT tbl_CourtCase.CaseNo, tbl_CourtCase.CountyNo, tbl_CourtCase.jDescr, tbl_CourtCase.sevClsCodeDescr
FROM tbl_CourtCase
LEFT JOIN (
SELECT DISTINCT T.CaseNo, T.CountyNo, 1 as ID
FROM tbl_CourtCase as T
WHERE (T.caseNo Like "*CF*") AND
((T.Jdescr = "Found Guilty at Court Trial") Or
(T.Jdescr = "Found Guilty at Jury Trial") Or
(T.Jdescr = "Guilty - No Additional Sentence") Or
(T.Jdescr = "Guilty Due to Alford Plea") Or
(T.Jdescr = "Guilty Due to Guilty Plea") Or
(T.Jdescr = "Guilty Due to No Contest Plea"))
) AS subQ ON (tbl_CourtCase.CountyNo = subQ.CountyNo) AND (tbl_CourtCase.CaseNo = subQ.CaseNo)
WHERE ((subQ.ID Is Null))
ORDER BY tbl_CourtCase.CaseNo, tbl_CourtCase.CountyNo, tbl_CourtCase.ChargeNo;

zipnotic

ASKER

OMG, that is really effing Close!

Possible to not include blanks/nulls so only case 2012CF000178 is returned?

zipnotic

ASKER

I think this is it!!! You see any problem with it?

-added = ""
-added = "extradited"
-added DISTINCT
removed chargeNo, jdescr to allow distinct

SELECT DISTINCT tbl_CourtCase.CaseNo, tbl_CourtCase.CountyNo
FROM tbl_CourtCase LEFT JOIN (SELECT DISTINCT T.CaseNo, T.CountyNo, 1 as ID
FROM tbl_CourtCase as T
WHERE (T.caseNo Like "*CF*") AND
((T.Jdescr = "Found Guilty at Court Trial") Or
(T.Jdescr = "Found Guilty at Jury Trial") Or
(T.Jdescr = "Guilty - No Additional Sentence") Or
(T.Jdescr = "Guilty Due to Alford Plea") Or
(T.Jdescr = "Guilty Due to Guilty Plea") Or
(T.Jdescr = "Extradited") Or
(T.Jdescr = "") Or
(T.Jdescr = "Guilty Due to No Contest Plea"))
) AS subQ ON (tbl_CourtCase.CaseNo = subQ.CaseNo) AND (tbl_CourtCase.CountyNo = subQ.CountyNo)
WHERE (((subQ.ID) Is Null))
ORDER BY tbl_CourtCase.CaseNo, tbl_CourtCase.CountyNo;

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

You might want to change:

(T.Jdescr = "") Or <= from your code

to:

(T.Jdescr & "" = "") or

This would capture those records where Jdescr is NULL or an empty string ("").

zipnotic

ASKER

AWESOME!

This gives me expected results. I'll have to check it over but holy conolly that was rough. Owe you one!

SELECT DISTINCT tCharge.CaseNo, tCharge.CountyNo INTO test1
FROM tCharge LEFT JOIN (SELECT DISTINCT T.CaseNo, T.CountyNo, 1 as ID
FROM tCharge as T
WHERE (T.caseNo Like "*CF*") AND
((T.Jdescr = "Found Guilty at Court Trial") Or
(T.Jdescr = "Found Guilty at Jury Trial") Or
(T.Jdescr = "Guilty - No Additional Sentence") Or
(T.Jdescr = "Guilty Due to Alford Plea") Or
(T.Jdescr = "Guilty Due to Guilty Plea") Or
(T.Jdescr = "Extradited") Or
(T.Jdescr = "Order of Acquittal") Or
(T.Jdescr = "Discharged After Being Found Incompetent") Or
(T.Jdescr = "Charge Consolidated into Another Case") Or
(T.Jdescr = "Other") Or
(T.Jdescr = "Guilty but Not Guilty Due to Mental Disease/Defect") Or
(T.Jdescr = "Information Filed") Or
(T.Jdescr = "Found Not Guilty at Jury Trial") Or
(T.Jdescr = "Administrative Disposition") Or
(T.Jdescr = "Charge Dismissed but Read In") Or
(T.Jdescr = "Amended Complaint Filed") Or
(T.Jdescr = "Amend") Or
(T.Jdescr LIKE "DEF*") Or
(T.Jdescr & "" = "") or
(T.Jdescr = "Guilty Due to No Contest Plea"))
) AS subQ ON (tCharge.CountyNo = subQ.CountyNo) AND (tCharge.CaseNo = subQ.CaseNo)
WHERE (((subQ.ID) Is Null))
ORDER BY tCharge.CaseNo, tCharge.CountyNo;

zipnotic

ASKER

Dale took plenty of time to work through a complicated query with me which ended up being great. Many thanks to a great and knowledgeable person.

Dale Fye

Glad to help.