If you want inspiration, remember Edison's comment on the subject: Genius is 1% inspiration and 99% perspiration.
You are discussing methods of categorization but I'm not sure how Access would even play into this unless you are using it as a "broker" to manage file repositories. I'll give you some specific advice and some general advice.
For the extensive structure you are describing, if Access is going to be an active participant, you probably would do best to become familiar with the features available in the FileSystemObject (a.k.a. FSO - which is part of the Scripting library). You will find a LOT (and I do mean a LOT) of methods and properties that expose the file system to Access automation. This explicitly includes file copy and move actions, folder enumeration (i.e. create a collection that you can examine), delete and rename files, etc.
But before you can manipulate the structures via FSO, you must first decide what properties/abilities you need within this structure. Like, what categories of file do you need? You mentioned recruitment, attendance, medical history, training, etc. But etcetera CANNOT be there. Oh, I suppose you could have Miscellaneous - but you need to keep file categories well-defined since the purpose of this exercise IS to manage a multitude of files.
Your idea of keeping files in a folder-by-person-name with a sub-structure of folders could lead to a huge complex of folders. How many people are we talking about? If a couple of dozen, then you might be talking about 200 total folders or less. If a couple of THOUSAND, then you are describing a massive infrastructure overhead in which you might have only one file in many of those folders, but EVERY FOLDER, because a folder IS a file, can occupy not less than one disk allocation unit (or in older nomenclature, one disk cluster). NO fractions, NO sharing of allocation units between files. On modern disks, this might be 4KB per unit, and thus 8 MB worth of sparsely populated directories for 2000 people BEFORE you store any actual files. That would make this a "high overhead cost" solution to your problem.
You might wish to consider that instead of the complex sub-structure, ask this question: How many files of each type will there be per person? Can you get by with having the person's name for a folder structure and having the multitude of files co-exist in that single folder, differentiated by something in the file name? Or perhaps is it better to have the sub-structure of directories by file intent and just load up individual files with names that identify the person - so you would have these files, from dozens to thousands, in the folder with the person's name tag or ID number or SOMETHING that can be used to separate them?
This kind of analysis has to start with an "expectation analysis" - that is, how many files of each type will you expect to have in the first year? In the 2nd year? In the 5th year? And how long must you keep them? How big will they be? How often will they be altered (if at all)?
Next question... so you have all of these files. So... what are you going to do with them? If this is archival storage, rarely touched once stored, could you use a ZIP folder as opposed to a normal file folder?
Next question... do any of these files involve regulatory compliance issues? I saw "medical history" so if this is USA, then HIPAA drops into place as a MAJOR consideration. I'm sure there would be other things that crop up as well. So how are those things going to play with your files - or how are your files going to play by those rules? Either way you ask the question, it has to be answered.
I've just scratched the surface here. This is where the perspiration comes in - tracking down expectations. A lot of this will involve "skull sweat" and your brain will eventually hate you before this is over.
I had similar issues because I worked on a mainframe that had an ORACLE DB for the U.S. Naval Reserve. The WHOLE USNR. I cannot legally tell you how many people were involved, but I can say without breaking too many rules that it was more than a four-digit number worth of people. We had to fight that storage organization problem and decided to make "category" directories with "people" files as opposed to "people" directories with "category" files/folders. The overhead was cheaper and it didn't actually matter since the computer could do the searches either way. We still had terabytes worth of data because of transaction history rules and hundreds of thousands of files to manage.
Hope this helps you focus on the true magnitude of the problem so that you can make some headway with it.
