Results 1 to 4 of 4
  1. #1
    Theflow is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2014
    Posts
    3

    Question Case Management - Multiple 'note' recors for each case

    Hi all,

    First time poster here after some guidance if you please - I used to dabble in Access quite a bit some years ago, but took a bit of a hiatus and unfortunately can't remember half of it!

    I'm looking for a bit of help with a case management system. A bit of a background - we currently have an excel spreadsheet we use to track cases over a number of years (there's currently circa 500 cases on it). Each individual case contains various key details about it such as the case name, who referred it, who's dealing with it and so forth. Each case also has what we refer to 'high-level comments', basically a text field for the case handler to put a brief description of the case background and actions etc.

    As time has gone on, the team that uses this tracker has grown somewhat and we also find ourselves in a position that requires a bit more of a robust series of notes to be collected for each case, something that we don't really have the functionality to be doing within the excel spreadsheet.

    Hopefully that's sufficient background. What I'm looking for some help with, is how to develop this concept into an Access (2007) database.

    At present, I have got a tables/forms etc set up with all of our case names and relevant details, however I'm stumped for how to create appropriate 'case notes' for each of these entries. I need, for each case, users to be able to input a note into the database and for it to capture the date / time that the note has been entered. This wouldn't be an issue if it was just one note entry that was updated each time, but I need there to be a new note entry each time a user adds a new comment.

    I can see this being possible if I were to create a table for every single case on the main case table, i.e ending up with ~500 individual tables, one for each case, which contain a series of notes as individual records. Now obviously this would be a horrendous pain to create / maintain and probably make the file absurdly large. Is there a better way of being able to create such a thing?



    Hopefully that makes sense - any questions just ask!

    Cheers,

    JW

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    No. You do not create a table for each case.
    You have 1 table , with hundreds of cases, / individiuals.

    1 table for individuals
    1 table for cases.

    Large is not a problem.

  3. #3
    Theflow is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2014
    Posts
    3
    Thanks for your reply.

    I'm not quite sure I understand how would you capture individual notes (along with the date/time that such a note was created) on those tables?

    My case table looks somewhat like the below (apologies, I cannot upload or download files from work):

    Case table:

    Case ID Date Case Created Case Name Case Handler Referring Colleague Referrer's Area
    1 27/10/2014 Joe Bloggs Alan Palmer Stacey Quinn Trade

    I'd then want the database user to be able to open this case up on a form, where they would be able to read previous comments (along with when/who they were input by) and enter a new one should they so choose. The creation of the form etc is not a problem, but I'm having real trouble grasping how I'd set up the notes for each case.

    Design wise, I'm thinking the top of the form would capture the case details from the Cases table, then would display the relevant notes underneath, something like this:

    Date of Note: Added by: Note Content:
    27/10/2014 11:20 Alan Palmer Initial note re case comments
    27/10/2014 11:35 Philip Coole Further update with additional info
    27/10/2014 11:45 Alan Palmer Case resolved due to...

    These entries would all be related to the one case for Joe Bloggs, and each case would have its own unique notes etc. I hope this makes sense - I'm just lost as to how I would set this up in the background table wise for all of the data to be captured as such.

    Thanks,

    JW

  4. #4
    Theflow is offline Novice
    Windows XP Access 2007
    Join Date
    Oct 2014
    Posts
    3
    Doh! After mulling this over the for last few days and drawing a blank, it's only just hit me as to how to do this, perhaps it was trying to explain it to somebody else that's jogged my memory - have a separate table for the Notes where each note is its own record. These records can include the 'Case Name' field which can then be used to match each note to its relevant entry on the Cases table.

    I'm not sure that's the most efficient way of doing it, but it seems to make sense to me and looks like it works from a quick test! If you do have any further thoughts on this please let me know, but many thanks!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Case Management Template request
    By rosscortb in forum Database Design
    Replies: 1
    Last Post: 09-17-2014, 02:20 PM
  2. Case for in case field corrupt
    By Ruegen in forum Forms
    Replies: 9
    Last Post: 08-03-2014, 07:56 PM
  3. Replies: 2
    Last Post: 02-12-2013, 07:27 PM
  4. Replies: 5
    Last Post: 10-23-2012, 03:55 PM
  5. case statement - multiple columns
    By eddiec in forum Queries
    Replies: 2
    Last Post: 07-05-2011, 03:24 AM

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