Hello everyone,
I’m new to Access, mostly self-thought, but I have an upcoming 2-day training to become an advance user. Meanwhile, I’m still trying to make headway in my database and I’m hoping that there might be some experts with great tips or a different perspective to assist me. (I’m using Access 2013)
I’ve read somewhere that the planning phase of a database is very important, so I gathered all the information and then divided the content into various tables, and tried to create the relationships, while encompassing every possible scenario.
Basically, the database will have a member’s information, the charges the member is accused of, and the case’s information. Each case will one or multiple hearing(s) in front of a Judge.
You can see the breakdown of my tables in the pictures Model 1 and Model 2 below.
For me, the best way to find my 1-to-many & many-to-many relationships was to actually write some examples. Please tell me if I’m wrong!
Scenario 1:
1 Case: 1 member with 4 charges
Docket 201351; Name: Walks: 2 charges on breach of trust, 2 charges on sexual assault
Scenario 2:
1 Case: 2 members, 1 charge
Docket 201424; Names: McComb & Gibsan : 1 charge of conduct to the prejudice of good order and discipline
Scenario 3:
1 Case: 2 members, 2 charges
Docket 201518; Name: McKenzie : 1 charge of conduct to the prejudice of good order and discipline;
Name: MacKenzy: 1 charge of Assault.
Scenario 4:
1 Case: 1 member with 1 charge
Docket 201703; Name: Walks (same member as Scenario 1): 1 charge on Absent without leave.
Note: It was decided in my office that if 4 years later, Mr Walks gets charged again, a new member record would be added to the database vice associating a new CaseID to a previously used MemberID. Just in case the member has different information under that table. This way the database will have the accurate information at the time of the offence for each court martial. (it is very possible that the member is now in a new unit or might have a new rank (if promoted or demoted).
This note just made the relationship between CaseT and MemberT a 1-to-many, since the same MemberID will never have more than 1 CaseID.
So these scenarios brought me to build two possible models:
Model 1:
Model 2:
Now comes the tricky part for me, using Model 1 and model 2 above, I created a form based on [CaseT], then tried adding two subforms for the member(s) and for the charge(s) that fall under a specific CaseID.
I tried creating subforms from the original tables (MemberT and ChargeT), tried creating subforms from queries based on MemberT, AccusedT, ChargeT.
There was always something that didn’t work, when I fixed one, another issue would pop up.
Usual issues:
-Too many records would pop up under a specific CaseID (directly related to wrong query parameters (probably selected a PK instead of a FK, or picked too many FK from tables I shouldn’t have touched).
-Subforms new data entry was not possible or subforms entry would be allowed but record would not show up in the form/subforms, it would only show if I manually opened the Table with all the records.
-I haven’t figured out a way to have both subforms (the members and the charges) of the Case form, to relate to one another (I’m trying to have the junction table [AccusedT] fill itself based information that will be entered on the subforms. (Possible solution: Make query of Junction table the subform’s record source) (other possible solution: create unbound form, with the member’s and Charge’s subforms, then add that unbound form as a subform of the CaseForm)
Even if I resolved the above mentioned issues, I’m left with something that is missing; I need a way to link the charge(s) to a member (I only want to see the charges and members that fall under that case, then be able to associate one with the other). In other words, filling the [AccusedT] table.
If you like visual aid, here is what the form and subforms look like:
^ in this example, I should only have 1 record, but for some reasons, I see four, and all of them have the same member, this issue is due to the parent/child relationship of the subform, I just don't have another picture with the correct info.
^ in this attempt, I was trying to create a combo box to select a member that this charge was related to. I stopped since I realized, what is if the charge is assigned to multiple members? I have to change my approach on this.
Anyway, i'll end my post here, it's getting pretty lengthy. sorry about that.
any assistance is much appreciated, even if it's a nudge in the right direction!
I’m new to Access, mostly self-thought, but I have an upcoming 2-day training to become an advance user. Meanwhile, I’m still trying to make headway in my database and I’m hoping that there might be some experts with great tips or a different perspective to assist me. (I’m using Access 2013)
I’ve read somewhere that the planning phase of a database is very important, so I gathered all the information and then divided the content into various tables, and tried to create the relationships, while encompassing every possible scenario.
Basically, the database will have a member’s information, the charges the member is accused of, and the case’s information. Each case will one or multiple hearing(s) in front of a Judge.
You can see the breakdown of my tables in the pictures Model 1 and Model 2 below.
For me, the best way to find my 1-to-many & many-to-many relationships was to actually write some examples. Please tell me if I’m wrong!
Scenario 1:
1 Case: 1 member with 4 charges
Docket 201351; Name: Walks: 2 charges on breach of trust, 2 charges on sexual assault
Scenario 2:
1 Case: 2 members, 1 charge
Docket 201424; Names: McComb & Gibsan : 1 charge of conduct to the prejudice of good order and discipline
Scenario 3:
1 Case: 2 members, 2 charges
Docket 201518; Name: McKenzie : 1 charge of conduct to the prejudice of good order and discipline;
Name: MacKenzy: 1 charge of Assault.
Scenario 4:
1 Case: 1 member with 1 charge
Docket 201703; Name: Walks (same member as Scenario 1): 1 charge on Absent without leave.
Note: It was decided in my office that if 4 years later, Mr Walks gets charged again, a new member record would be added to the database vice associating a new CaseID to a previously used MemberID. Just in case the member has different information under that table. This way the database will have the accurate information at the time of the offence for each court martial. (it is very possible that the member is now in a new unit or might have a new rank (if promoted or demoted).
This note just made the relationship between CaseT and MemberT a 1-to-many, since the same MemberID will never have more than 1 CaseID.
So these scenarios brought me to build two possible models:
Model 1:
Model 2:
Now comes the tricky part for me, using Model 1 and model 2 above, I created a form based on [CaseT], then tried adding two subforms for the member(s) and for the charge(s) that fall under a specific CaseID.
I tried creating subforms from the original tables (MemberT and ChargeT), tried creating subforms from queries based on MemberT, AccusedT, ChargeT.
There was always something that didn’t work, when I fixed one, another issue would pop up.
Usual issues:
-Too many records would pop up under a specific CaseID (directly related to wrong query parameters (probably selected a PK instead of a FK, or picked too many FK from tables I shouldn’t have touched).
-Subforms new data entry was not possible or subforms entry would be allowed but record would not show up in the form/subforms, it would only show if I manually opened the Table with all the records.
-I haven’t figured out a way to have both subforms (the members and the charges) of the Case form, to relate to one another (I’m trying to have the junction table [AccusedT] fill itself based information that will be entered on the subforms. (Possible solution: Make query of Junction table the subform’s record source) (other possible solution: create unbound form, with the member’s and Charge’s subforms, then add that unbound form as a subform of the CaseForm)
Even if I resolved the above mentioned issues, I’m left with something that is missing; I need a way to link the charge(s) to a member (I only want to see the charges and members that fall under that case, then be able to associate one with the other). In other words, filling the [AccusedT] table.
If you like visual aid, here is what the form and subforms look like:
^ in this example, I should only have 1 record, but for some reasons, I see four, and all of them have the same member, this issue is due to the parent/child relationship of the subform, I just don't have another picture with the correct info.
^ in this attempt, I was trying to create a combo box to select a member that this charge was related to. I stopped since I realized, what is if the charge is assigned to multiple members? I have to change my approach on this.
Anyway, i'll end my post here, it's getting pretty lengthy. sorry about that.
any assistance is much appreciated, even if it's a nudge in the right direction!