Hello,
I have been working on a database on and off since the winter, and I'm at a really good place now. However, I think I've discovered a flaw in my design. I am trying to design a database where my team can track the different orthopedic referral requirements by surgeon - because every surgeon doesn't operate on every body part, and even if you have 5 surgeons who all do the same procedure, they may have different imaging requirements and other necessities (e.g. must have patient history, does not accept pediatrics, does not accept knee problems if arthritis present, etc).
So I have a tbl_PrimaryProblem (main body part like ankle, knee, spine, etc) which is connected to tbl_SubProblem (meniscus tears, bunions, hardware revision, etc). I also have another table called tbl_ReferralRequirements (x-ray within 6 months, MRI sufficient if no OA, no imaging required, etc). The subproblems and referralrequirements are joined to a junction table called tbl_Process where each Process primary key identifies the unique process by surgeon/problem/subproblem/requirements.
I have created 2 types of forms:
1) Search by sub-problem. It has 2 cascading combo boxes (primary problem, then secondary problem) which then produces a list of surgeons who does the specified problem, and then we can look down the list to see who requires what. I've locked this record so no one can accidentally change the data in the tables.
2)Search by surgeon, but this form is for entering a new "process" per surgeon. You search by name, and it brings up their unique processes in a subform. So for example, I learn that Dr. X is now doing ACL surgeries when he wasn't previously, I can enter the new subproblem, referral requirements, etc. in this form and the data gets saved to the tables.
THE PROBLEM: In the subform, I have fields for PrimaryProblem_FK and SubProblem_FK, however 1) it seems redundant to list the primary problem since it's already linked to the subproblem in the backend, BUT, it's a good prompt for those using the database to remember what primary problem they are entering for and 2) it's leaving room for huge error as I could enter "Knee" - "bunion" which do not match at all.
Is there a way to choose a primary problem, and have that limit the options in the subproblem field? Sort of like cascading combo boxes, but within a data-entry subform?
Thanks in advance, let me know if I need to explain more.