Forms dealing with many-to-many junction table

[SimonD]

Hi everyone, I’m at the stage to create forms that the user will be able to fill and/or edit data, but due to the “enforce referential integrity” rule I created, I know there is certain information that needs to exist prior to fill a junction table. If you check the image:

Initially I created a form (#1 on the image) for the docket # and preferral date, only two pieces information on it. From there I wanted to create a button and the macro would open a window with form #2 where the user would enter all the charges (but the charges need to be associated to that docket #) So far the only thing I can do and fill in charges, but they are not linked to the docket number
Then my second form, would also have a button to save and open a window with the third form. This third form would have information to fill from two tables, the member(s) and their information, and somehow the association of which member with each charges (still under the same docket #).
(Since 1 member could have many charges, and many members can have one unique charge under the same docket)
Unless I need a fourth table, let’s say based on the junction, and there somehow associate which member(s) is associated to which charge(s) and also link the combination of members and charges to a docket number.
Any insights, suggestions are appreciated.
Thank you

 

[bob fitz]

I may not understand your requirement correctly but I think you just need a sub form bound to tblJunction on the main form.

 

[plog]

I think you may be right in that you need a new table--I'm not entirely certain where it would go though. Maybe these questions will help:

1. What's the difference between tblmember data and tblMemberInfo data? Why aren't they in the same table?

2. Why isn't tblSentence related to a case, docket or charge? I bet that should be linked to tblJunction and not tblmember.

Lastly, tblCharge is more of a reference table. It should be populated with common charges and rarely would you add a new one. If a person gets accused of a crime never before prosecuted, that's when you should open it up and add to it. From a form perspective, tblJunction would be a subform on the form based on tblCase. In that subform would be a dropdown to populate the ChargeID field. You wouldn't go to a seperate form to add charges to a tblCase, you would select one from the table that already exists.

 

[SimonD]

I think you may be right in that you need a new table--I'm not entirely certain where it would go though. Maybe these questions will help:

1. What's the difference between tblmember data and tblMemberInfo data? Why aren't they in the same table?

tblmember contains information about a member that does not change over time.
tblmemberinfo contains other info (i.e. rank and unit) of the member at the time of a specific trial.

If the same member gets charged again in 10 years, that member will most likely have a different rank and be at a different unit, but his last name and service number will not have change. When I run a search in the database for the old trial, I want to be able to see the rank and unit at the time of a specific case. If I had the information from tblmemberinfo inside the tblmember, a new trial would mean that the information such as rank and unit will overwrite the old info from that member. I hope I explained it right.

2. Why isn't tblSentence related to a case, docket or charge? I bet that should be linked to tblJunction and not tblmember.

This table wasn't part of my question, but I'm still not sure where to link it, since, contrary to findings, which a Judge gives per charge per member (located in the tbljunction), a sentence is given per docket per member. (if 3 member, 8 charges in one trial, there will be only 3 sentences but (if all 3 members are charged with all 8 charges) there will be 24 findings)

Lastly, tblCharge is more of a reference table. It should be populated with common charges and rarely would you add a new one. If a person gets accused of a crime never before prosecuted, that's when you should open it up and add to it.

I initially had the charges as a reference, but each statement of the offence is unique. And the charge designation varies from trial to trial. For the sake of normalization I could certainly extract some of the content here into another table, but the main issue remain the same, I would have a table with the stateOfTheOffence unique to each charge associated with a one to many to the junction table.

From a form perspective, tblJunction would be a subform on the form based on tblCase. In that subform would be a dropdown to populate the ChargeID field. You wouldn't go to a seperate form to add charges to a tblCase, you would select one from the table that already exists.

I'll think on this, and will reply shortly. you gave me an idea (modified version of your idea).

 

[plog]

1. tblMemberInfo, tblMemberInfo and tblJunction need a new relationship:

A. MemberId comes out of tblJunction.
B. There will be no direct relation from tblmeber to tblJunction
C. MemberID goes into tblMemberInfo.
D. tblmember has a 1-many relationship with tblMemberInfo.

~~~

2. You really need to pick one term and stick with it, do not use synonyms. Your explanation introduced the terms 'findings' and 'docket', there are no tables in your image that correspond to those. Perhaps they are synonyms for something else (Case, Hearing?), but I don't know. Synonyms will kill communication in technical settings.

Please restate this response with better terms, or rename your tables so I know what you are talking about.


~~~~

3. If a unique description goes with the charge, then you can put that in tblJunction (assuming that's where ChargeID is going to end up at).

 

[SimonD]

1. tblMemberInfo, tblMemberInfo and tblJunction need a new relationship:

A. MemberId comes out of tblJunction.
B. There will be no direct relation from tblmeber to tblJunction
C. MemberID goes into tblMemberInfo.
D. tblmember has a 1-many relationship with tblMemberInfo.

~~~

2. You really need to pick one term and stick with it, do not use synonyms. Your explanation introduced the terms 'findings' and 'docket', there are no tables in your image that correspond to those. Perhaps they are synonyms for something else (Case, Hearing?), but I don't know. Synonyms will kill communication in technical settings.

Please restate this response with better terms, or rename your tables so I know what you are talking about.


~~~~

3. If a unique description goes with the charge, then you can put that in tblJunction (assuming that's where ChargeID is going to end up at).

1. I decided to combine (again) the tblmember and tblmemberinfo, and in the end, there will be more than one record when a member is charge again, I will have very few duplicate, since it does not happen that often. One way to eliminate a problem, plus, this also resolve my issue if I had a woman (maiden name) with a service number that gets charge, then a few years later, if she gets charge again but this time with a married name, I would suddenly have a duplicate service number but different last name. But simply having a new record everytime we have a repeat offender(member), that will resolve my issue.

2. Docket = Case (it is in the table under case)(I should rename the table for those not use to the terminology.

With respect to finding, it is in the junction table, not a table in itself, since it is the perfect location for it, since there is a finding for each combination of member and charge.

3. you mean put the foreign key? or merge the charge table with the junction table? (because I cant do that, if 2 members are accused of one charge (each member have a different charge) but both are in the same trial (case/docket).

Basically: (I clean the stuff to focus on one thing)

I filled manually in each table some data to test. For the junction table I have a bunch of association like memberID 1, chargeID1, docketId1
memberId1, chargeID2, docketID1, etc

I don't want the user to have to fill in those numbers, I'm hoping to create a form with subforms or whatever that works, to have the user create a new docketID, then fill all the members that fall under that docket, then all charges that fall under that docket, then somehow associate which member is charged with which charge(s) that are/is on that docket.

I don't want the user to see all charges from the database or all members from the database when doing that association, just those that fall under a specific docket.

 

[plog]

I'm hoping to create a form with subforms or whatever that works, to have the user create a new docketID, then fill all the members that fall under that docket, then all charges that fall under that docket, then somehow associate which member is charged with which charge(s) that are/is on that docket.

You've broken the relationship in your mind with that. People and charges need to be assigned to a docket at the same time (by filling out a form based on tblJunction). This is going to have to be done in some manner by the user.

I'm really questioning tblCharges now. My thought was it was sort of like a reference table that held data like statute number, criminal infraction description,. A very generic set of data. Your description makes it seem like its a hybrid of that and then instance specific details (officers report of incident, etc.) I don't know if this table is properly normalized.

Can you provide some sample data that could appear in it?

 

[SimonD]

You've broken the relationship in your mind with that. People and charges need to be assigned to a docket at the same time (by filling out a form based on tblJunction). This is going to have to be done in some manner by the user.

I'm really questioning tblCharges now. My thought was it was sort of like a reference table that held data like statute number, criminal infraction description,. A very generic set of data. Your description makes it seem like its a hybrid of that and then instance specific details (officers report of incident, etc.) I don't know if this table is properly normalized.

Can you provide some sample data that could appear in it?

tblCharge

Charge Desig/NDAref/otherlaw/otherlawRef/StatementOfTheOffence/Alt?

First Charge / 129 / - / - / Neglect to the prejudice of good order and discipline / -

Second Charge / 129 / - / - / Conduct to the prejudice of good order and discipline / -

Third Charge / 130 / 86(1) / Criminal Code / Carelessly stored a firearm / -

Fourth charge / 130 / 4(1) / Controlled Drugs and Substance Act / Possession of a substance included in schedule II / -

Fifth charge / 95 / - / - / Ill-treated a person who by reason of rank or appointment was subordinate to him / -

Notes: NDA ref means the article referenced in the National Defence Act
Other law : the charge may also be an offence on the civilian side, not just the military side of the law.

As you see in the first two examples, both are charges under article 129 of the NDA, but have different Statement of the offence. And there could be many variation since the writing is left to the crown prosecutor. I have to put in the database, whatever is written on the charge sheet. I guess I could write most of the possible variation and have the user select one, and if not in it, add it to the database has an option besides the others.

And then these charges on a charge sheet, could be for one accused (member) or multiples in the same docket.

I'm thinking I will do as you proposed and instead of having the tblCharge have unique items, have it as a reference table, and assigned the charges to the docket by members. The only issue with this is, when there is only one charge on the charge sheet, but it applies to two members (accused), statistically speaking there is only one charge on this docket, but as a reference table, it will appear as two charges, one for one member, and one charge for the other member.

 

[plog]

Seems as if [Charge Design] and [StatementOfTheOffence] is related to the Charge/Member/Case and not the actual charge. That means those fields belong at a higher level.

If the exact same tblCharge record can be used for multiple members of the same case, then you do need another junction table. This would sit between tblCase and tblJunction. So, I would name it tblCaseCharges and it would have these at least these fields:

tblCaseCharges
ChargeCaseID, autonumber, primary key
DocketID, number, foreign key to tblCase
StatementOfTheOffence

Now, I don't know enough about your data, but you need to decide where the other fields that are currently in tblCharge go. It could be in tblCaseCharge, or tblCharge remains as it is and holds just generic data about charges themselves.

NDAref is just such a generic charge field. It would go in tblCharge if you have more generic data about the charge that goes with it. I suspect [ChargeDesign] is related to the Member therefore it goes in tblJunction.

Lastly, tblJunction loses [DocketID] but gains [ChargeCaseID]. You will now have two junction tables between cases and members.

Make those changes and post another screenshot.

 

[SimonD]

I took the screen shot, from the computer I work from, then transferred the screen shot on the computer that has internet to post to you, then realized I forgot to remove the extra relationship. This laptop does not have the updated MS Access, and other computer I work on access with, does not have access to forums due to firewall.

Note: the luw (tables on the right) means they are "look up wizard", will be like a combo box choice.

 

[plog]

No problem, I see that you understand what I meant. Looking at tblCase I see a few fields about Withdrawn. Should those be attached to the charges or the case? Maybe they both have withdrawn dates. Again, you know your data better than I, just asking a question.

For forms, with this set up, I see it working like so:

User opens Case form and adds basic data. Button gets clicked to open Member form--this form allows them to search for members and add them if they don't exist, no assignment is made for members at this point. They close this form and return to the Case Form. There is a sub-form based on tblCharges that allows them to add charges to the Case. Next to each charge record is a button that opens the Junction form to that specific ChargeID. On the Junction form the user can add members to charges. When done, they exit back to the Case form and can go to the next charge.

That's how I envision the forms will work. However, I think the next step is using this new table structure to generate the output you will need. If you need any reports build them before the forms. Let's make sure the table structure works for providing you with the output you will need.

 

[SimonD]

Edit: I wrote this post below at the same time you replied this post above^

If I understand this: each unique member entry (from tblMember) is related to a docket through the charge(s) assigned to him/her.

strFinding (under tblJunction) is still under the proper table, since a finding (verdict) for each charge associated to a member will result from a docket (case).

Now, when it comes to filling new info, due to the enforced referential integrity relationships, the tblJunction cannot be populated until the records are entered under tblCharge and tblMember.
And same for the tblCharge, records cannot be populated before a record is created in the tblCase.

So the logical order of the forms: info on the case (docket) then filling in info on the charge(s) and member(s) with 2 subforms maybe. Save the records, then another form on the tblJunction that links which charge is tied to which member?

 

[SimonD]

Now, how to relate the tblSentence to this.

A member gets one sentence per case (may have multiple sentences over time if he/she has multiple case)

A case can have multiple sentences if multiple members on trial. (only one sentence per member)

A case may have no sentence if findings = not guilty

in the original post, I had tblSentence linked to tblMember, do you think it is still in the proper location?

 

[plog]

No problem. Just to confirm, my prior post agrees with just about everything you just posted. The only thing is that I don't think tblMember can be a subform on the Case form. Its down to far in the relationship. If anything it would have to be a subform on tblJunction, but I think an independent form where you first enter members is the best way to go.

Again though, reports are next.

 

[SimonD]

No problem, I see that you understand what I meant. Looking at tblCase I see a few fields about Withdrawn. Should those be attached to the charges or the case? Maybe they both have withdrawn dates. Again, you know your data better than I, just asking a question.

I put ysnChargesWithdrawn (meaning it is a yes/no field if all charges were withdrawn for this case) in the tblCase because I also wanted the datWithdrawn (date all the charges were withdrawn) besides datTerminated (which is the date the case is terminated when finding/sentence are given by the Judge) If a case does not have a date terminated, it must have a date all charges were withdrawn. If not I will have a report that shows all ongoing cases, even the ones that are adjourned for 6 months. I hope I explained it properly.

For forms, with this set up, I see it working like so:

User opens Case form and adds basic data. Button gets clicked to open Member form--this form allows them to search for members and add them if they don't exist, no assignment is made for members at this point. They close this form and return to the Case Form. There is a sub-form based on tblCharges that allows them to add charges to the Case. Next to each charge record is a button that opens the Junction form to that specific ChargeID. On the Junction form the user can add members to charges. When done, they exit back to the Case form and can go to the next charge.

That's how I envision the forms will work. However, I think the next step is using this new table structure to generate the output you will need. If you need any reports build them before the forms. Let's make sure the table structure works for providing you with the output you will need.

Brilliant, I like the way your suggestions on the order and interaction between the forms. I will work on queries first, since most of my report are based on that. (I also need to fill in more fake data in the database to be able to test my queries and report!)

thank you.
Now I need to figure out how the button (in the subform, besides each charge) opens the junction under that specific charge !

 

[SimonD]

what do you think of post #13?

 

[plog]

I don't think you need a sentence table, that data should just go in tblCharge

 

[SimonD]

i think i'll put the sentence in the tblMember it makes more sense to me. Since there is one sentence per member (regardless of how many charges that member is accused of)

by putting it under charge, I will have a record for a sentence attached to each charge, and I don't want that.

 

[SimonD]

There is a sub-form based on tblCharges that allows them to add charges to the Case. Next to each charge record is a button that opens the Junction form to that specific ChargeID. On the Junction form the user can add members to charges.

I cannot picture how to do this. Would you create a query based on the tblmembers and also have tblJunction ChargeID and MemberID?
then create a form based on that query?

 

[plog]

I cannot picture how to do this. Would you create a query based on the tblmembers and also have tblJunction ChargeID and MemberID?
then create a form based on that query?

My forms go like this--Main form is based on 1 record in 1 table, subforms are continous forms which show multiple records from another table (the many side). So the Case form shows 1 case record and has a Charge subform which shows all charges of that case. You can add/edit data to that subform which go to the Charge table. Besides each of those charges is a button.

Click the button and go to the Charge form which shows 1 record from the Charge table. It has a subform on it that shows all records in tblJunction related to that charge. In that subform you can add/edit records to tblJunction.