See example in previous post, would that match your description of a many-to-many relationship?Reversing the arrows on the Junction table will give you a better result. Perhaps I can explain it from a mechanical viewpoint.
You are trying to implement what is called a "many to many" relationships between people and cases. Access doesn't DIRECTLY support that - but it DOES support the Junction table concept. So if you want to use the junction table to enumerate the relationships between two tables with a "many to many" relationship, you want the "many" sides to be on those two tables. The "one" side in the Junction table is because that table enumerates each individual link between a person and a case. Since a person could in theory be charged many times and since a case can involve many accused, the Junction table is merely a list of links.
I thought of it, that’s why it is there in my new relationships diagram! It made sense!However ... there is more to consider. You (correctly) said that you have to move the Finding elsewhere because you can actually have multiple disparate findings for a single case with multiple persons. I'll make a suggestion.
Since a person can face more than a single charge and a multi-person case will possibly offer multiple findings, you need to put the finding in a place that is unique to a given combination of person and case. As it so happens, the junction table is a unique place where that combination is already expressed.
You would have lost your bet on this one, in the military, no such thing. Hearings are just a “session” where the Court is convened, if more than 5 days between adjournments or days with court closed, then it is simply another hearing under the same docket #. If the convening order change, it is simply another case (another docket number). So the relationship will always be 1 case to many hearings (or to 1 hearing, it enough to complete the process).In your earlier post, 2nd option, you had "Member" linked to "Charge" via the junction table. But you had other tables for which similar concepts might appear. For instance, you had hearings with a simple link between "Case" and "Hearing." But I'll bet that there is such a thing as a continuance or deferral. Does that count as a separate hearing? Or a separate session?
If a member is charged again, I want my database to have a new entry for that member. Since the information associated with that member, such as rank, unit, etc, might change over time, and I want the database to reflect the accurate information on the member for that particular case. (I don’t want to update the information on the member for the latest case, since if I look at that member for the old case, I want to be able to see what unit he/she was back then).I'm betting dollars to donuts that some of the people you would list in the Case table are the same cast of characters for a lot of cases? If so, a junction table between Case and "Cast of Characters" might help you take care of some useful queries like, how many cases involve Panel Member John Q Public?
My issue is the interaction between three main tables; I’ll go read up on “dependency”Pardon me if I step into teacher mode. Your central junction table is what we sometimes call the Intersection of its component tables - in this case, Members and Cases. This is where those two sets overlap. There are thus three major places in your data - things that are unique to a Member; things that are unique to a Case; and things that are unique to the combination of a Member and a Case. So when you try to decide where stuff goes, think along the lines of "Does this depend ONLY on the Member?" "Does this depend ONLY on the Case?" "Does this depend on the combination?" That is how you decide where to put things.
Now expand that way of thinking to all of your tables. When you read up on Database Normalization, you will see the "dependency" question in at least some of the references. And that isn't an accident or a happy coincidence.
Past a certain point, some of this discussion will have to fall to you since you are the one who is immersed in the environment where this occurs. We are on the outside looking in, even if doing so with educated eyes. But if we give you good advice, you will quickly learn by doing.
Good luck.
I'm betting dollars to donuts that some of the people you would list in the Case table are the same cast of characters for a lot of cases?
If a member is charged again, I want my database to have a new entry for that member.
Hearings are just a “session” where the Court is convened, if more than 5 days between adjournments or days with court closed, then it is simply another hearing under the same docket #.
I have created [PersonnelT] that encompasses all staff, from jury (called panel members in the military), to the lawyers, court reporter and finally the Judge. JSpecifically was not considering the accused member, but rather the persons who act as panel members. Legal staff members? Since this is military, members of the JAG group or whatever is your equivalent? You've got a separate table for the accused persons; I was thinking of a "Staff" table for the OTHER entries such as were in the CaseT table of your first post - mostly panel members and alternate panel members. But if that is a jury picked by open lot from a broad population, it is different from a hearing panel where you have multiple court officers. Since I don't know what those positions actually represent, I'll just say that it bears consideration.
It is my understanding that it will track the sessions. By creating a 1-to-many relationship between the [CaseT] and the [HearingT], I will have from 1 to many hearing(s) linked to that unique Case, and each hearing entry will keep track of dates, location, type of hearing, etc. There are no such things as split session or continuance in the military, easier to keep track in this database, I guess!OK, but this STILL engenders a question. Would you need to track that session in some way, perhaps if it involved a split session or continuance? That is the question YOU have to answer for yourself, of course - not for me.
I will remove all the content of the [ConclusionT] and shove it back into the [CaseT]. And regarding the arrow towards “Sentence”, I agree with you, even though here in my office, the admin clerk, who fills the current excel databases, is under the opinion that regardless the amount of offenders (offender: an accused found guilty) there is only one sentence where the Judge enumerates them. For the purpose of keeping track, I will link them to each member under a case.Post #23 - look specifically at the link from ConclustionT through CaseT. First, are those actual fields listed or the statuses? (AllChargesWithdrawn seems incongruous with regard to the rest of the content.) The position of the arrow also is confusing since "Sentence" surely is an individual result but "Decision Published" is a case result. Ditto for "Appeal". Also, you show one/one relationship between case and conclusion. If you have multiple Accused for a case, wouldn't a split conclusion be possible?
I have created [PersonnelT] that encompasses all staff, from jury (called panel members in the military), to the lawyers, court reporter and finally the Judge.