Looking for a new perspective! (1 Viewer)

[SimonD]

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!

 

[SimonD]

Model 1 or Model 2? (or any other suggesions?)

Any suggestions on how have my junction table be filled, without the end user seeing the CaseID, MemberID and ChargeID, which will mean nothing to them.

 

[plog]

Tables, then reports and the queries necessary to build them and finally forms. That's how you should develop. No sense making amazing forms if at the end of it all you can't get the data you need out of your system.

So, with that in mind, let's focus on your tables for the near term. Model 1 is incorrect because it has circular relationships. That is, you should only be able to trace one path from any table to another. You've created a loop which allows 2 ways between MemberT and CasT. So this model is incorrect.

Model 2 looks correct, but there's issues with your tables/fields. In general, you've made the error of storing value information in field names. You've done this in 2 manners in CaseT:

Numerated field names--> when you start suffixing field names with numbers, its probably time for a new table to hold that data. So, all those fields that end in a number most likely need their own table. You've allowed multiple accused per case by having an accused table, you need to do the same thing with all those fields.

Type values in field names -> you are also storing types related to your values in field names. For example, PanelMemberX and AlternatePanelMemberX. That 'Alternate' prefix should be a value in a table, not a name of a field.

Combining those 2 things above, you need a PanelMember table that would be structured like so:

PanelMembers
MemberID, autonumber, primary key
CaseID, number, foreign key to CaseT
Member, ?, this will hold whatever value is currently in all those fields
MemberType, Text, 'Alternate', 'Regular' or 'Senior'

That eliminates those 8 member fields you currently have in CaseT. Additionally, if those suffixed numbers were important, you can add a MemberNumber to the table to hold it. Often though, suffixed numbers in field names have no purpose other than to differeintate field names.

You need to do that for Members as well as other fields of that ilk you have done that with (e.g. _assignedX fields)

Work on your tables, then we can move on.

 

[MarkK]

I don't understand the purpose of the accused table. What does it mean that a member is connected to a charge via an 'accused'?

 

[SimonD]

Tables, then reports and the queries necessary to build them and finally forms. That's how you should develop. No sense making amazing forms if at the end of it all you can't get the data you need out of your system.

So, with that in mind, let's focus on your tables for the near term. Model 1 is incorrect because it has circular relationships. That is, you should only be able to trace one path from any table to another. You've created a loop which allows 2 ways between MemberT and CasT. So this model is incorrect.

Thank you for taking the time to point me in the right direction, now I understand what's wrong with Model 1. And yes, I understand the value of not wasting time on forms when the database isn't working properly, you should have seen me at the beginning, I thought my database was perfect without testing and I was doing lots of cosmetic change and images. When I realized it wasn't working. I completely stopped on the cosmetic part. Now, I mostly use the automatic layout done by the wizard.

Model 2 looks correct, but there's issues with your tables/fields. In general, you've made the error of storing value information in field names. You've done this in 2 manners in CaseT:

Numerated field names--> when you start suffixing field names with numbers, its probably time for a new table to hold that data. So, all those fields that end in a number most likely need their own table. You've allowed multiple accused per case by having an accused table, you need to do the same thing with all those fields.

Type values in field names -> you are also storing types related to your values in field names. For example, PanelMemberX and AlternatePanelMemberX. That 'Alternate' prefix should be a value in a table, not a name of a field.

Combining those 2 things above, you need a PanelMember table that would be structured like so:

PanelMembers
MemberID, autonumber, primary key
CaseID, number, foreign key to CaseT
Member, ?, this will hold whatever value is currently in all those fields
MemberType, Text, 'Alternate', 'Regular' or 'Senior'

That eliminates those 8 member fields you currently have in CaseT. Additionally, if those suffixed numbers were important, you can add a MemberNumber to the table to hold it. Often though, suffixed numbers in field names have no purpose other than to differeintate field names.

You need to do that for Members as well as other fields of that ilk you have done that with (e.g. _assignedX fields)

Work on your tables, then we can move on.

Thank you, I knew i needed new perspective! It is a great idea!

 

[SimonD]

I don't understand the purpose of the accused table. What does it mean that a member is connected to a charge via an 'accused'?

My purpose here is to create a many-to-many relationship between the charge(s) and the member(s).

I picked the name "Accused" since it was fitting. A military member that has allegedly committed an offence (charged but not yet found guilty or not guilty). Which is exactly what that table links, members to charges.

One member may have multiple charges, and a charge may have multiple member associated to it.

Hope this helps

 

[pbaldy]

Post 5 was moderated, posting to trigger email notifications.

 

[plog]

As I look at CaseT, I see a lot of dates and people, right? That means it's decision time, and since you know your data/system best this is squarely on you.

Normlization would say you need a table for people associated with the case and with dates associated with the case. For example you would take all that data out of CaseT and put it in 2 new tables like so:

CaseRoles
CaseRoleID, CaseID, Role, Person
1, 13, Lead Court Reporter, Smith
2, 13, Prosecution, Jones
3, 13, Assisting DC, Phillips
etc.

CaseDates
CaseDateID, CaseID, CaseDate, DateType
1, 13, 1/2/2017, Preferral
2, 13, 1/5/2017, Convene For
3, 13, 2/1/2017, Terminated
etc.

However, that may not be the best way for you. It really depends on how you need to use that data, you may stick with what you have now. If you needed to know all cases for which Jones was part of (in any role, Assisting, DC, etc.) the normalized way I listed above would be the way to go. It really depends on how you need to use those Dates and Names.

 

[The_Doc_Man]

many-to-many

The "arrows" are wrong for the Accused table it if is truly intended as a many-to-many relationship. The Accused table is the ONE side of a one/many relationship to BOTH of the tables that it is joining as a Junction Table. It represents one INSTANCE of a person being accused of one charge (if I read your intent correctly). You have it as the MANY side of both relationships. Done correctly, this would enumerate the persons and charges involved in your case ID.

And by the way, the arrow from Accused is wrong for the case ID, too. To my limited understanding of legal issues, you would never have two cases for the same person and same charges for the same incident. There would be perhaps multiple charges for the same person and same legal reference but two events during an incident, maybe? I.e. let's say your person assaults someone, goes away, and comes back and does it again. To my limited understanding, that is TWO counts of the same person, same law, maybe as part of a separate incident. So EITHER your case details multiple events or your structure lacks capacity to show multiple events that are part of the same overall incident (and therefore same case).

Others have commented on the lack of proper normalization for your CaseT fields that have numbers in them, as many as 1-5. E.g. AssignedMember, CR_Assigned, Pro_Asssigned, DC_Assigned. This SCREAMS for one or more child tables.

You really need to read up on "database normalization" to understand why your CaseT table design is both wasteful and self-limiting at the same time. Not to mention inflexible.

Between what I've thrown your way and the comments of the other members, I'm sure you'll have a lot to look at before you come back with more questions. Since that's kind of the way we operation anyway, that won't be a problem.

However, I'm going to pass along one more bit of advice. (Maybe two...)

Old Programmer's Rule #1: If you can't do it on paper, you can't do it in Access. What this means in practical terms is that you must remember, you are the subject matter expert. Access by itself, with nobody to guide it, is passive. It's as dumb as a box of rocks. YOU will be providing the elements you need for Access to automate this for you. Which means you need to have a blueprint or guideline document that details what you want to achieve.

Old Programmer's Rule #2: Access won't tell you anything you didn't tell it first. What that means is, in a variant of the old "Garbage In, Garbage Out" rule, if you don't give Access a way to store or compute what you needed it to provide for you, you have done yourself no favors. Decide ahead of time what you wanted to see and then assure that Access has a way to find that, get that, compute that, whatever... And that includes a part of the design phase where you start with (a sample of) the desired results to verify that you have ways to get everything you wanted to see.

Good luck. Give our answers some thought. I'm sure you will have more questions.

 

[jdraw]

Just to say I agree with Doc and the other's comments. Create some sample scenarios re reports and queries. Then get a data model
on paper and using some test data and ensure your tables and relationships permit you to get the answers/results for your test scenarios.
Then use your model to create the database (tables and relationships). You'll save time if you don't start with physical access.

Good luck.

Also: I recall a thread a few years back dealing with Judges and Cases in Ontario...is this related in any way?????

 

[MarkK]

One member may have multiple charges, and a charge may have multiple member associated to it.

Your Charge table has a Finding field. So what happens if there is one Charge, two Members, but the Finding is different for the different members? Your design doesn't allow for that outcome. Maybe the 'accused' table should be called 'finding' or 'judgement' instead. In terms of language, aren't the accusation and the charge are the same thing?
Mark

 

[SimonD]

Thank you everyone, I will read and take the time to answer all of you, I just came out of court this morning, so now I have time to review, make changes, and work on this!

 

[SimonD]

As I look at CaseT, I see a lot of dates and people, right? That means it's decision time, and since you know your data/system best this is squarely on you.

Normlization would say you need a table for people associated with the case and with dates associated with the case. For example you would take all that data out of CaseT and put it in 2 new tables like so:

CaseRoles
CaseRoleID, CaseID, Role, Person
1, 13, Lead Court Reporter, Smith
2, 13, Prosecution, Jones
3, 13, Assisting DC, Phillips
etc.

CaseDates
CaseDateID, CaseID, CaseDate, DateType
1, 13, 1/2/2017, Preferral
2, 13, 1/5/2017, Convene For
3, 13, 2/1/2017, Terminated
etc.

However, that may not be the best way for you. It really depends on how you need to use that data, you may stick with what you have now. If you needed to know all cases for which Jones was part of (in any role, Assisting, DC, etc.) the normalized way I listed above would be the way to go. It really depends on how you need to use those Dates and Names.

Hi again plog, those are very good suggestions, I'm starting to see and think as a database builder with your suggestions, but, as you said, I will probably leave the dates and lawyers name in this table, there is no requirement to have any statistic on the lawyers. With respect to dates, I might do it, since there are statistics (reports) my boss will want based on number of days between preferral of charges and date the trial was convened. So I will look into that, but not just yet.

 

[Pat Hartman]

One scenario that is missing from your verbal description but which option 2 seems to solve is
1 case; 2 members with 1 charge, second member with 1 charge

So in this case, some members have the same charges bot at least one member has additional charges or perhaps each member has totally different charges although I'm not sure how that would work. But in the first case, both members participated in the robbery but only one of them is charged also with assault.

 

[SimonD]

The "arrows" are wrong for the Accused table it if is truly intended as a many-to-many relationship.

my understanding was that I created a many-to-many junction table between the Member's Table and the Charge's Table, then that junction table (AccusedT) was in a many-to-one relationship with Case Table. I'm trying to understand your point here, I thought to create a many-to-many relationship, you had to create a junction table that has a one-to-many relationship with the two tables you want that many-to many relationship.

The Accused table is the ONE side of a one/many relationship to BOTH of the tables that it is joining as a Junction Table. It represents one INSTANCE of a person being accused of one charge (if I read your intent correctly). You have it as the MANY side of both relationships. Done correctly, this would enumerate the persons and charges involved in your case ID.

So is this a many to many relationship table?

And by the way, the arrow from Accused is wrong for the case ID, too. To my limited understanding of legal issues, you would never have two cases for the same person and same charges for the same incident.

Once again, I thought my table relationships were reflecting this. So my table is completely wrong, because that’s what I want, what you just explained. I thought by putting the “1” on the [CaseT] side, I was having a possibility of many members and charges associated to one case.

In my first post, I wrote (under scenario 4, see note) that my office decided that a member will have a new memberID if charge again, we will not like a previously used memberID to a new CaseID. So the CaseT will always be on the receiving end of a many-to-one relationship.

Others have commented on the lack of proper normalization for your CaseT fields that have numbers in them, as many as 1-5. E.g. AssignedMember, CR_Assigned, Pro_Asssigned, DC_Assigned. This SCREAMS for one or more child tables.

You really need to read up on "database normalization" to understand why your CaseT table design is both wasteful and self-limiting at the same time. Not to mention inflexible.

I am checking out normalization, I’ll polish my database! And thank you for the Old Programmer’s rules. Good food for thought!

 

[SimonD]

Your Charge table has a Finding field. So what happens if there is one Charge, two Members, but the Finding is different for the different members? Your design doesn't allow for that outcome. Maybe the 'accused' table should be called 'finding' or 'judgement' instead. In terms of language, aren't the accusation and the charge are the same thing?
Mark

This is very true, I have to move Finding out of the [ChargeT]. Finding will be per member, if two members in a case (regardless number of charges), I will have 2 findings.

You got me thinking of sentence as well.
Sentence will be per member, it could be nothing has sentence if the finding is:
Not Guilty
Not Guilty (No evidence offered)
A stay of proceedings
Not Guilty (No Prima Facie)

or it could range from 1 to many punishment. I need another table now!

 

[SimonD]

One scenario that is missing from your verbal description but which option 2 seems to solve is
1 case; 2 members with 1 charge, second member with 1 charge

So in this case, some members have the same charges bot at least one member has additional charges or perhaps each member has totally different charges although I'm not sure how that would work. But in the first case, both members participated in the robbery but only one of them is charged also with assault.

yes, I'm hoping the database also covers that possible scenario

 

[The_Doc_Man]

SimonD, here is some general advice, prompted by me seeing this from you:

This is very true, I have to move Finding out of the [ChargeT]. Finding will be per member, if two members in a case (regardless number of charges), I will have 2 findings.

Excellent. You are beginning to see for yourself that your data layout didn't exactly model your "real world" operation. This is an INCREDIBLY important lesson. When reality says A and the database seems to say B, MOST of the time the database is wrong.

There is an old phrase that we always want to avoid for our databases - don't let the tail wag the dog. In any argument between design and reality, reality must ALWAYS win. This was the origin of many of the comments about your representation. Get as close to reality as you can.

And may I offer a word of encouragement? Seeing that fact for yourself AND realizing how to approach it means you are on the right track. It just needs a little bit of "skull sweat."

 

[SimonD]

Hi again The_Doc_Man, have you seen the reply to your post on page 1. I thought my table relationships (from model 2) were correct, but your post has me on pause now. Let me know what you think of my post (post #15).

 

[The_Doc_Man]

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.

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.

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?

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?

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.