I have a database that tracks patient referrals and incoming reports.
Current state tables:
With this setup, I have a form that displays the selected patient's info at the top, then displays all of their referrals, and then displays all reports that are linked to each referral. There's a separate section in the form for any reports that are not tied to a referral (separate table, as well, see above).
I need to change this so that all reports are in one table, only, instead of two. However, I also need to retain the ability to link reports to referrals OR not. I.e., when a referral is created, there's a ReferralID that's autogenerated. Then, when logging a report, it can either be tied to a referral via ReferralID, or not by leaving the ReferralID blank. However, I'm unsure how to set the form up to allow this capability. I realize I might be too vague here, so please ask me questions.