I'm designing a database for a case manager. The layout I have so far has a main form with a tabular control that includes six tabs for different categories of information: General Wellness, RN Problem List, Medications, Assessments, Care Plans, and Outcomes. The first three tabs contain sub-forms that store information in a Patient Demographics table, which also stores information input from the main form. The last three tabs contain sub-forms that contain another tabular control with nine tabs for nine diagnosis. Each diagnosis tab contains a sub-form which stores information in the respective diagnosis table.
I would like the user to be able to search for a patient on the main form and have the main form and each sub-form return the records for that patient. The user needs to be able to update and save the record once returned. I have experience with very basic databases, but I am unsure what the relationships in this database should be and whether the sub-forms should be based off of the tables or off of queries of the tables in order to make it work the way I want it to. Any help would be greatly appreciated!