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