Count

[lakshmi_pili]

I got this report which is group by Police Officer, with an arrest date, incident, LastName, First Name, and Violation. The way it works now is that the officer may display a total count of 9 because there were nine Violations - which may include two violations for the same person. The problem I have is that not only do I want it to count how many arrests the officer had (which I can get it to do), but I also want to count only the number of arrests.

If there were a total of nine violations that happened, but two of those violation pertained to Smith, Jane, that means that I would only have a count of seven arrests (or sever individuals) for that officer. I then would like to get a total sum for arrests (or individual)

I am linking 3 tables, and I think that may be the problem.

If anyone can suggest anything, I would appreciate it.

 

[Fornatian]

Seems to me that you have the arrest field in the wrong table. Say you have three tables.

Table 1: Person Details
Table 2: Incidents
Table 3: Violations

Table 1(Person Details) would have one to many relationship with Table 2(Incidents) because each person can have many incidents. Table 2(incidents) would alsoh have another one to many relationship between itself and the Violations Table because each incident can carry a number of violations.

Assuming the above is correct, then the fact that the person was arrested should be recorded with the incident and not the violation.

If it is that your table is already built like this then it may be that the field that you are counting is in wrong place on the report, instead of having it in the detail area of the report, move it up a heading so that it sits in the same section as the incident details.

It may also be that the calculation is in the wrong place, try moving it around the sections until you get the figure you're looking for.

Hope some of that applied.

Ian