Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2013
    Posts
    4

    Question Unanswered: Help counting multiple checkboxes in a form to make a total in a report

    I’m learning Access 2007 by experimentation and by doing myself without the benefit of formal training, hence I’ve run into a stump (probably an easy solution for some, but not for me … but I know I’ve come to the right place …). I’m working on a database for police detectives that should track investigative progress on cases assigned to them.

    On my main data entry form there is a 14 digit “CaseNumber” for each investigative case. There will be as many as two individuals identified as “victims” and one other individual identified as a “witness”. There will be as many as three more individuals identified as “suspects”. Each of these individuals will be identified by their names (first, middle, last, and surname (if any). For each of the three “suspect” individuals I have included a checkbox (Y/N) labeled “yes” and another checkbox (Y/N) labeled “no” to identify any of the three individuals as having been arrested for that particular “CaseNumber”. There is other information to be entered as well, but presently I am interested in the individuals identified as “suspect”s.

    Here’s my difficulty: on one of the reports that I have been trying to make I would like each of the “suspect” individuals with a checkmark in the “yes” checkbox to be listed on their own line in the report along with some other supplementary information. If there is only one “suspect” with a “yes” checkbox arrested in that case, he is listed on the report on his own line with that particular “CaseNumber” along with the other information. (No problem there....).

    However, my question is: if more than one “suspect” in one particular case has been arrested with the “yes” checkbox marked, how do I get each of these (“suspect” #2 and/or #3) to be listed each on their own line using the same “CaseNumber”? I know that I could formulate a new report and list each individual (if only one) or all individuals (if more than one) that have been arrested on a single line, but that’s not what I want to do. I want my report to list them on separate lines and to count them as a total number of individuals arrested.

    I apologize for the length of this request for assistance. I hope that it adequately explains my problem. Rest assured that any assistance or advice will be greatly appreciated by this po’ boy. Thanks in advance.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There is a fundamental flaw in your database design which does not conform to the rules of databases normalisation (normal forms: A Simple Guide to Five Normal Forms in Relational Database Theory).

    In your model, whant happens if:
    A case implies more than 2 victims?
    A case implies more than one witness?
    A case implies more than 3 suspects?

    Ideally, there should be a table (say: Tbl_Cases) only containing data related to a case, a table (say: Tbl_People) only containg data related to people (whether thy are victim, suspect, witness or whatever) and a junction table (Database Design - Many-to-many, Junction table - Wikipedia, the free encyclopedia, Create a many-to-many relationship in Access) that relates one or more line from the Tbl_People table to one or more line from the Tbl_Cases table (i.e. a case can have any number of victims, witnesses, suspects and any individual in Tbl_People can be related to one or several cases as a victim, a witness, a suspect).

    This junction table should have a Foreign key column pointing to the primary key of Tbl_Cases and a Foreign key column pointing to the primary key of Tbl_People, plus several columns indicating the "status" of the individual in a particular case (victim, witness, suspect, arrested, etc.) About Primary keys and Foreign keys, see: Primary Key Definition: What is a Primary Key?, Tables and the primary key, Foreign key - Wikipedia, the free encyclopedia, Teach-ICT AS Level ICT OCR exam board - database terminology, among many others.

    A more complete model should have a fourth table (say: Tbl_Status) that would enumerate every status a person can have and the Tbl_Junction would then use a third foreing key pointing to the primary key of Tbl_Status. If you're not familiar with the relational model, you can drop this extension, at least for now.

    About the Relational model and databases normalization, see: Relational model - Wikipedia, the free encyclopedia, http://tinman.cs.gsu.edu/~raj/4710/f11/Ch03.pdf, http://databases.about.com/cs/tutori...oc/simple5.htm, Database normalization - Wikipedia, the free encyclopedia, http://www.iai.uni-bonn.de/III//lehr...DWA/WS07/1.pdf, again among many others.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •