Page 1 of 2 12 LastLast
Results 1 to 15 of 29
  1. #1
    Mersad's Avatar
    Mersad is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    10

    Arrow Law Firm Database - How to manage people and cases


    Dear readers,

    I have been following these forums a lot while designing my database, and have finally decided to register. This is my first major database, so please forgiveme if i am not as proffesional as you guys are.

    I am creating a Law Firm Database. I have created the tables and relationships between them. I am currently stuck on the question how to handle the people in the database against the various cases.

    One table accomodates all the people that can be associated with a case. I first thought about dividing it into clients and opponents, but quickly came to the realisation that sometimes people associated with opponents can become clients and vice versa.

    My question is, how do i pull this off in a form, where i want to display clients and opponents on one case (one record). Between the people table and cases table i have created a many to many relationship.


    Thank you in advance for your answers, this has been bugging me a lot and I hope you can help me.
    Attached Thumbnails Attached Thumbnails image.jpg  

  2. #2
    alcapps is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    292
    usually we would create a many to many table.

    id
    caseid
    clientID
    Stats

    joe smith = client1
    jane doe = client2
    jim doe = client3

    caseid = 1 ... joe smith against jane doe
    witness.. could be for and against..
    you could use True for for and False against as well..

    so the table would start out with..

    1,1,1,for
    1,1,2,Against
    1,1,3,for
    1,1,3,Against..

    now you have a relationship between cases, and clients and their stance in that case.. weather they are for and against or both.

    hope this helps..

  3. #3
    Mersad's Avatar
    Mersad is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    10
    Thank you for your answer. I have created a many to many table.

    1. Is that the table i should be creating the main form from?
    2. Is it possible to create two subforms from the same table (Clients Table) through which I can list clients and opponents in one record that belongs to a specific case?

  4. #4
    alcapps is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    292
    just make a unbound form.. first

    in that form make a subform for cases..

    and a subform for both for and against.. just make the data have a where clause that has for and against in each one.
    before insert.. just make the me.stats = for.. or me.stats = against depending on which form your in..

    good luck to you..

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    to the forum...

    Setting up a normalized structure is part Art, part Science. There are a lot of very talented people that are willing to help, but it helps to know where you are in the process.
    What tables/fields do you have so far? Would you post them? A pic of the relationship window?

    Since you are in the design phase, use only letters, numbers and/or the underscore in object names - no spaces or special characters.
    No look up FIELDS (different than look up tables). See http://access.mvps.org/access/tencommandments.htm and http://access.mvps.org/access/lookupfields.htm
    Be aware of reserved words. See http://allenbrowne.com/AppIssueBadWord.html

    Will more than one person be using the dB? If so, it should be split into a front end (FE) and a back end (BE).

    And so begins your journey....

  6. #6
    Mersad's Avatar
    Mersad is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    10

    Relationships in the Court Case Managemnet Database CCMS

    Quote Originally Posted by ssanfu View Post
    to the forum...

    Setting up a normalized structure is part Art, part Science. There are a lot of very talented people that are willing to help, but it helps to know where you are in the process.
    What tables/fields do you have so far? Would you post them? A pic of the relationship window?

    Since you are in the design phase, use only letters, numbers and/or the underscore in object names - no spaces or special characters.
    No look up FIELDS (different than look up tables). See http://access.mvps.org/access/tencommandments.htm and http://access.mvps.org/access/lookupfields.htm
    Be aware of reserved words. See http://allenbrowne.com/AppIssueBadWord.html

    Will more than one person be using the dB? If so, it should be split into a front end (FE) and a back end (BE).

    And so begins your journey....
    Thank you very much for replying. You are all very helpful here! I have read many books on Access (Missing Manual, Access 2010 Bible, Step by Step, etc...) but until you start making your own, it's all just going through the motions.

    I am very interested in Access and have become a bit obsessed in designing, normalizing and crafting the overall design of the database, to be honest.

    Since English is not the language in which I am creating my database I have included a relationship screenshot that is translated, so you guys can follow me.

    Click image for larger version. 

Name:	ccms1.jpg 
Views:	60 
Size:	75.0 KB 
ID:	11121

    I have 7 Tables

    1. People
    (In which all people are listed no matter if they are clients or opponents, because as I have previously written sometimes an opponent can become a client, and vice versa, so it makes sense that for the sake of normalization all persons that occupy a role in one case (except judges) are listed in one table)

    2. Cases (all information in relation to a specific court case)

    3. Courts (information regarding courts)

    4. Judges
    (information regarding judges)

    5. Fees
    (information on court ordered fees)ž

    6. CaseActions
    (particular steps taken in each case)

    7. People_Cases
    (joint table for the many to many relationship between cases and persons)


    And these are the relationships (and rules of the database) I want to create:

    1. Every person (People Table) can have many cases.

    2. Every case can accommodate many persons (on the side of clients and opponents).

    3. Every case can only have one court, but every court has many cases.

    4. Every case can only have one judge, but every judge has many cases.

    5. Every case has many fees, but every specific fee can only be related to one case.

    6. Every case has many actions, but every specific action can only be related to one case.




    Let me know what you think, and we'll go from there. Thank you so much for helping me!!

  7. #7
    alcapps is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    292
    People_cases you can add a field for for or againstUnder judges you can remove courtid not needed unless you did it for some special reason.

    you probably need an actions table to list all the differs actions you will do. The case actions handles the case actions but not the list of actions that can be pre filled
    People_cases probably needs a field for type of person to that ccase which needs a type of person table.

    I usually make a general list table
    id
    listname
    fieldid
    fieldvalue
    sortorder
    isactive

  8. #8
    Mersad's Avatar
    Mersad is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    10
    Quote Originally Posted by alcapps View Post
    People_cases you can add a field for for or against
    But what if a person is a client and opponent at the same time (for example an insurence company that is sued, but that you represent on another unrelated case)


    you probably need an actions table to list all the differs actions you will do.
    Do you mean the general list table by this? I am not sure what information to store in that table, because if a case is active, what steps were last taken, etc. are listed in the Cases Table.


    People_cases probably needs a field for type of person to that ccase which needs a type of person table.
    What fields would you create in that table, and is it in a one-to-many relationship with People_Cases?

    Thank you for your fast responses, you're awesome!

  9. #9
    alcapps is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    292
    question 1..
    yes but each case has an entry in the many to many table..

    people_Cases table entry
    people_casesID, PeopleID,CasesID,Side
    1, 1, 1, for
    2, 1, 2, against

    the table handles the same person in many cases for and/or against

    id
    case 1
    people 1
    for

    id
    case 2
    people 1
    against

    you would never be a for and against in the same case right?

  10. #10
    alcapps is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    292
    question 2.

    consider cases table..

    you might have a list of values that you want to be in the case category field

    so I would setup a combo box for that field in the general list

    1, CaseCategory, 1, Assault, 10, -1
    2, CaseCategory, 2, Perjury, 20, -1
    and....so.. on..Not a Lawyer.. not sure how you want to have your categories

    following the table layout I gave you..

    Case Notes might be a table as well..

    CaseNotes
    ID
    CaseID
    Notes
    Date
    People(employee)

  11. #11
    alcapps is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    292
    question 3..
    People_Cases
    would have PeopleTypeID

    tied to the below.. fieldID

    I would use the general list table..

    making up the id.. use Autonumber

    14,PeopleType,1,Attorney,10,-1
    15,peopleType,2,Plaintiff,20,-1
    and so forth..

  12. #12
    alcapps is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    292
    Also for database design.. we usually remove "s" off of tables.

    we already know it is more than one fee.. but you are storing a fee. So the table name is Fee.

    People
    PeopleCase
    Fee
    CaseAction
    Case
    Court
    Judge
    GeneralList

  13. #13
    alcapps is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    292
    you could add Country to your general list
    used in People

    you could add FeeSort to your General list
    used in Fees

    you could add Action to your General List
    used in CaseActions

    you could add
    CaseRole
    CaseSuccess
    CaseCategory
    CaseStatus
    to your general list
    used in Cases

    You don't have to tie GeneralList to any tables.. in your Relationships..to the fields that you have the values..

    Anyway food for thought.. hope this helps

  14. #14
    Mersad's Avatar
    Mersad is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2013
    Posts
    10

    Steps after table design

    Quote Originally Posted by alcapps View Post
    question 1..
    yes but each case has an entry in the many to many table..

    people_Cases table entry
    people_casesID, PeopleID,CasesID,Side
    1, 1, 1, for
    2, 1, 2, against

    the table handles the same person in many cases for and/or against

    id
    case 1
    people 1
    for

    id
    case 2
    people 1
    against

    you would never be a for and against in the same case right?
    You are right, and now i fully understand what you mean. THANK YOU SO MUCH, you have actually answered my initial question. I will do exactly as you told me.

    So far three persons will be using the database. Is there anything I should consider beforehand? I know I have to separate tables, queries, forms etc.

    This is where I would go from here, please tell me if you think this is ok.

    1. I would redesign the tables as you have suggested.

    2. I was thinking of creating a query where I would use a Builder to combine FirstName, LastName and Company (a client is either a company or a person, so there wouldn't be overlapping).

    3. From that query I would create a form that would list PeopleID and Side (from the People_Cases Table).

    4. I would use that form as a subform in my main form that would also show the related case.

    Thanks again, you have really helped me. I appreciate it a lot!!!

  15. #15
    alcapps is offline Competent Performer
    Windows 8 Access 2010 32bit
    Join Date
    Jan 2012
    Posts
    292
    if you need auditing for who put data in. You can consider the audit part of the tables.

    LastModBy
    DateLastMod
    CreatedBy
    CreationDate

    Table fees would be your candidate for that type of auditing.

    3.
    you won't know who is on a side until you have a case.
    so you list everyone who is people by client, witness or something like that and not already in your list

    so in a case..
    you enter a new case.
    you enter the client in people
    you enter all the people you know at that point witness or possible witness
    then you mark them to the case and for or against.


    you usually start with a unbound form with the tab control
    insert subforms in each tab to do the things your trying to do.

    look at the templates from microsoft they give you idea's of layout and how to use forms and subforms.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 10-05-2012, 04:57 AM
  2. Pull All Cases of a Client
    By nevets in forum Reports
    Replies: 1
    Last Post: 02-22-2012, 08:08 PM
  3. Mulit-User Querying Database 250 people
    By nguyeda in forum Database Design
    Replies: 2
    Last Post: 06-09-2011, 07:10 AM
  4. Replies: 0
    Last Post: 12-28-2009, 12:14 PM
  5. Best way to organize a small database of people?
    By Orabidoo in forum Database Design
    Replies: 1
    Last Post: 06-09-2009, 10:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums