I have been asked to assist an investigations company in revamping their medical investigations database and business process. The process goes something like this: They get a fax from a customer with the information they need to do the investigation. Major topics include: Requestor Info | Type of Service Requested | Location and Time Frame to Search (sometimes multiple) | Subject Info (doctor name, address, alias, license, etc.). They enter some of this info into a single Access table, then use a typewriter and a pen to enter the results onto a triplicate form. It's really a nightmare, and especially so because some of their big clients have asked for paperless results. As of now, they haven't even been logging the results into the database.
They seem to want me to create a huge table with a ton of fields. I don't think this is what I should do, but I am inexperienced in creating complicated relational databases. I will post a couple screenshots of what they have, and what I have created so far in the hopes that ya'll can point me in the right direction or let me know if I'm on the right track. The relationships seem to work, but creating forms for data entry when the data is spread between a few related tables has proven to be above my skill set. I need some help please.
I will include a form I created to try and replace the old paper triplicate form they were using. I created it in Livecycle Designer and had hoped to somehow link it to the Access DB. I think it will help outline the situation. The top half of the form is the info we get via fax. The bottom half is the info the investigator responds with.
Thank you so much for taking the time to help me. I have gotten really frustrated while developing this solution, especially since I think I spent a lot of time learning tech that doesn't directly apply. Times winding down on my completion date and I freaked.