Count Primary Key Once in Report

[Lettyg82]

I have a report that counts the number of Court Cases in a month. The primary key is [CaseNumber], when i run a report to show the charges for that case number, depending on the number of charges that case has tied to it it will count [CaseNumber] that many times. I only need to count [CaseNumber] once. See attached report sample. [CaseNumber] is a unique id as there is no other case that will have the same case number.

I should add that this report is generated from a query (qry_CourtCases) that has a parameter.

-The field [EventDate] has the following criteria in the query Between [StartDate] And [EndDate]
-the [EventType] from table CaseEvent is set to "TJ" or "TC"
-the [JTCT] field from the table Case is set to JT or CT


-the [EventType] from CaseEvent_1 is set to "SE"

SELECT Case.CaseNumber, Case.Specialist, Case.DefLast, Case.DefFirst, CaseCharge.Ct, CaseCharge.Charge, CaseCharge.MiscCode, CaseCharge.Dispo, CaseCharge.DispoRSN, CaseEvent.EventDate, CaseEvent.EventType, CaseEvent.EventOut, Case.JTCT, CaseEvent_1.EventDate
FROM (([Case] LEFT JOIN CaseEvent AS CaseEvent_1 ON Case.CaseNumber = CaseEvent_1.CaseNumber) INNER JOIN CaseEvent ON Case.CaseNumber = CaseEvent.CaseNumber) INNER JOIN (CaseCharge LEFT JOIN ChargeCD ON CaseCharge.MiscCode = ChargeCD.ChargeCode) ON Case.CaseNumber = CaseCharge.CaseNumber
WHERE (((CaseEvent.EventDate) Between [StartDate] And [EndDate]) AND ((CaseEvent.EventType)="TJ" Or (CaseEvent.EventType)="TC") AND ((Case.JTCT)="JT" Or (Case.JTCT)="CT") AND ((CaseEvent_1.EventType)="SE"));

[June7]

Wasn't this issue addressed in https://www.accessforums.net/reports...ext-27255.html

[Lettyg82]

Is it the same code for all the sums? just change the field names?

[June7]

Should be. I didn't test. Try it.

I deleted the other thread that essentially duplicates this topic.

[Lettyg82]

The code to count the checked boxes for Warrant or Subpoened does not work with: =Abs("*","Case","Warrant='True' AND Specialist='Kati Behrens' AND CaseDate=#[Enter Date]#") or =DCOUNT("*","Case","Warrant='True' AND Specialist='Kati Behrens' AND CaseDate=#[Enter Date]#")
also tried: =Sum(Abs("Case","WARRANT"=True AND Specialist='Kati Behrens' AND CaseDate=#[Enter Date]#")

"The expression you entered has a function containing the wrong number of arguments"

[June7]

Why are you using Abs function?

Note in my example form other thread there are no ' marks around True. You show ' marks in two of the expressions. That DCount should work without the ' marks. True is a boolean constant recognized by SQL and Access/VBA. Placing within single or double quotes defines it as a literal string.

[Lettyg82]

Why are you using Abs function?

Note in my example form other thread there are no ' marks around True. You show ' marks in two of the expressions. That DCount should work without the ' marks. True is a boolean constant recognized by SQL and Access/VBA. Placing within single or double quotes defines it as a literal string.

I thought DCOUNT is only for counting text fields, I am trying to count the number of check boxes that are ticked.

[June7]

You are counting records that meet the criteria. The checkbox state is one of the criteria. Did you try?

[Lettyg82]

=DCount("*","Case","Warrant=True AND Specialist='Kati Behrens' AND CaseDate=#[Enter Date]#") still gives me an error.

I tried

=DCount("*","Case","Warrant = True AND Specialist='Kati Behrens' AND CaseDate BETWEEN #" & [StartDate] & "# AND #" & [EndDate] & "#") and after i enter the parameter it gives me the fields [StartDate] and [EndDate] and the text boxes have an error.

I apologize i am confused. I am using the code you provided in the other thread adn that worked for one thing but not for this other report.

[Lettyg82]

NW DV FAST TRACK DATABASE.ziprptCourtCases.pdf

Somehow I feel like my 3 questions have been mixed up. I have 3 different reports each from a different parameter query, which is why i had submitted 3 seperate threads.

#1 Report: Court Cases Problem: The report is counting each case depending on the number of charges it has, i only want it to count the case number once.

SELECT Case.CaseNumber, Case.Specialist, Case.DefLast, Case.DefFirst, CaseCharge.Ct, CaseCharge.Charge, CaseCharge.MiscCode, CaseCharge.Dispo, CaseCharge.DispoRSN, CaseEvent.EventDate, CaseEvent.EventType, CaseEvent.EventOut, Case.JTCT, CaseEvent_1.EventDate
FROM (([Case] INNER JOIN CaseEvent ON Case.CaseNumber = CaseEvent.CaseNumber) LEFT JOIN CaseEvent AS CaseEvent_1 ON Case.CaseNumber = CaseEvent_1.CaseNumber) INNER JOIN (CaseCharge LEFT JOIN ChargeCD ON CaseCharge.MiscCode = ChargeCD.ChargeCode) ON Case.CaseNumber = CaseCharge.CaseNumber
WHERE (((CaseEvent.EventDate) Between [StartDate] And [EndDate]) AND ((CaseEvent.EventType)="TJ" Or (CaseEvent.EventType)="TC") AND ((Case.JTCT)="JT" Or (Case.JTCT)="CT") AND ((CaseEvent_1.EventType)="SE"));

[Lettyg82]

LongSheetRPT.pdf

# 2 Question: LongSheetKati Report: The query for this report is qryLONGSHEETKati
Problem: I can not get any expression to work =Sum, =DCOUNT to work. I believe because i have so many parameters on it.

I need a code to count the number of ticked boxes per case number, as well as the sum of #of victims and the other fields that have numbers in it, also need to sum of some text fields, such as total LPD (agency) cases.

This report has various parameters:

SELECT Case.CaseNumber, Case.CaseDate, Case.DefLast, Case.DefFirst, Case.[#Victims], Case.VictimPresent, Case.PriorDVConvict, Case.DIV, Case.DDA, Case.Specialist, Case.Agency, Case.CR, Case.Officer, Case.VOI, Case.Warrant, Case.VSubd, Case.VNAME, Case.VIS, Case.ChildrenPresent, Case.Children, Case.VPreg, Case.[A/D], Case.Inv, Case.Interp, Case.MutlArrst, Case.SS, Case.DefSex, IIf([defsex]="F",1,0) AS FDEF, Case.VSEX, IIf([VSEX]="M",1,0) AS VMALE, DIV.Judge, CaseCharge.Ct, CaseCharge.Charge, ChargeCD.CDCharge, CaseCharge.Dispo, CaseCharge.DispoRSN, CaseCharge.DTime, CaseCharge.Sentencing, CaseCharge.SentLength, Case.MonSob, Case.DVTX, Case.DrTX, Case.ADETX, Case.ANGRM, Case.PARENTC, Case.MentalHTX
FROM (DIV RIGHT JOIN [Case] ON DIV.DIV = Case.DIV) LEFT JOIN (ChargeCD RIGHT JOIN CaseCharge ON ChargeCD.ChargeCode = CaseCharge.MiscCode) ON Case.CaseNumber = CaseCharge.CaseNumber
WHERE (((Case.CaseDate)=[Enter Date]) AND ((Case.Specialist)="Kati Behrens"));

[June7]

The DCount is not dependent on the query report is based on. I tested the Warrant DCount on the Dismissed Cases report and it worked just like the DefSex calcs. It should perform the same way on any report.

[Lettyg82]

Ok now I'm even more confused. The Dismissed Cases report does not have a Warrant field in it, it is located in the table but not the query nor the report. How can the report count the warrant field (which is check marks) on the dismissed report? I want for someone to open a report for example the Court Cases report and it count the total number of cases that went to trial, but this is being counted 3 times because that case number or defendant was charged with three counts of criminal charges. At the end of my report it tells me that there are 3 cases that were dismissed which is not correct, it should only count the case number once.

What code are you using to calculate the sum of fields?

The Longsheet report parameter is to enter the date you want the report for, for example 08/01/12, the query is set to pull up all of Kati Behrens reports on date entered. The code below has two date field parameters and does not work on the longsheet report, how can that same code work for the report?

=DCount("*","Case","Dismissed = True AND DefSex='F' AND Specialist='Kati Behrens' AND CaseDate BETWEEN #" & [StartDate] & "# AND #" & [EndDate] & "#")

[June7]

The DCount is not dependent on the report RecordSource. Domain aggregate functions calculate directly on the table or query referenced in the function. It is irrelevant what fields are on the report. This is why the DCount needs all the same criteria specificed in its WHERE CONDITION argument that the report uses if you want the data synchronized.

The only coding I did was the DCount expression in textbox of report footer.

An alternative is a subreport in the report footer that provides the summary Case calcs.

[Lettyg82]

So =DCount("*","Case","Vsubd = True AND Specialist='Kati Behrens' AND CaseDate AND #" & [Enter Date] & "#") seems to work for the check boxes. Thank you so much for being ever so patient with my many questions.

BUT this code does not count to sum up the number of [PriorDVConvict] this is a number field, how do i manipulate the above code to SUM the number of prior convictions? I tried =DCount("*","Case","#Victims AND Specialist='Kati Behrens' AND CaseDate AND #" & [Enter Date] & "#") but i got an error.