Count Primary Key Once in Report

[June7]

Try:

DSum("PriorDVConvict","Case","Specialist='Kati Behrens' AND CaseDate BETWEEN #" & [StartDate] & "# AND #" & [EndDate] & "#")

Review http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

[Lettyg82]

That did not work but this did: =DSum("PriorDVConvict","Case","Specialist='Kati Behrens' AND CaseDate AND #" & [Enter Date] & "#")

It worked for all but one number field [#victims] could it be because of the # sign in the field name?

[June7]

That works? The WHERE CONDITION does not make sense. Did you mistype in the post?

If you include the field name in [] it should work; however, advise not to use spaces, special characters, punctuation (underscore is exception) in names nor reserved words as names just to avoid this issue.

[Lettyg82]

That works? The WHERE CONDITION does not make sense. Did you mistype in the post?

If you include the field name in [] it should work; however, advise not to use spaces, special characters, punctuation (underscore is exception) in names nor reserved words as names just to avoid this issue.

I changed the field name to [NoVictims] I also changed another name of a field that was giving me an error from A/D to [AD] and that fixed the problem.

=DSum("NoVictims","Case","Specialist='Kati Behrens' AND CaseDate AND #" & [Enter Date] & "#")

=DCount("*","Case","AD = True AND Specialist='Kati Behrens' AND CaseDate AND #" & [Enter Date] & "#")

Thanks again for all your help and patience with my no SQL knowledge. You explained a lot of things I was not familiar with.

I am only having 1 last problem, on my dismissed cases, I cannot figure out how to count the CaseNumber once as oppsed to 3 times (the number of charges a case has tied to it). Any helpful information that could help me solve that problem?

[June7]

I still don't understand that WHERE CONDITION you show. The part 'CaseDate AND #...' does not make sense.

I thought we already resolved how to use DCount on the dismissed cases report?

[Lettyg82]

The counting of duplicate records we did not address, we have only address the summing and totals of the reports - which all are fixed. The only problem remaining is the report counting the case number 3 times as opposed to one.

I wouldn't know any better on the WHERE CONDITION should I remove the AND? I think what I did was mimick what you had sent and made it match the parameter query which for the LongSheetRPTKati only requires one date not a BETWEEN date. The SQL code is as follows: SELECT Case.CaseNumber, Case.CaseDate, Case.DefLast, Case.DefFirst, Case.NoVictims, 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.AD, 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]

Well, that WHERE does make sense, so replicate it in the domain aggregate function calcs.

I don't understand your confusion on the DCount. It is DCount that I originally demonstrated. Just change the criteria as needed.

[Lettyg82]

Well, that WHERE does make sense, so replicate it in the domain aggregate function calcs.

I don't understand your confusion on the DCount. It is DCount that I originally demonstrated. Just change the criteria as needed.

The code is not working. I have tried different variations of the below:
=DCount("*","Case",[CaseNumber] (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")

I also tried going through Sorting/Grouping, adding header/footer and a text box with control source =1 but that is not working either. I believe this one is tricky because of the many parameters as well as the criteria.

[June7]

The example you show is referencing two tables - Case and CaseEvent. The reference to Case in second argument tells the function to look at the Case table to find data, then in the field names you prefix with table CaseEvent - CaseEvent.EventDate. The code will of course be confused by this.

You have [CaseNumber] just hanging there without any criteria parameter and no AND or OR operator.

You are not correctly using quote and apostrophe marks.

You apparently do not yet understand how to construct domain aggregate function expression. I have provided adequate examples of correct syntax for you to adapt for each required calculation. I can't keep going in circles trying to correct your misinterpretations. You need to study some tutorials on domain aggregate functions. Start with http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

Please make effort to study use of the functions and practice applying them. Good luck.

[Lettyg82]

The example you show is referencing two tables - Case and CaseEvent. The reference to Case in second argument tells the function to look at the Case table to find data, then in the field names you prefix with table CaseEvent - CaseEvent.EventDate. The code will of course be confused by this.

You have [CaseNumber] just hanging there without any criteria parameter and no AND or OR operator.

You are not correctly using quote and apostrophe marks.

You apparently do not yet understand how to construct domain aggregate function expression. I have provided adequate examples of correct syntax for you to adapt for each required calculation. I can't keep going in circles trying to correct your misinterpretations. You need to study some tutorials on domain aggregate functions. Start with http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

Please make effort to study use of the functions and practice applying them. Good luck.

You are absolutely correct, I am not a database designer and have pretty much self taught on how to make things work. Ive been getting by - by trial and error. I will take a look at the information you have provided. Thanks for you help.