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.
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).
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.