Reference tables quandary
I have 3 (Parent) reference tables: Dx Codes Reference, Pcs Codes Reference, and MS-DRG codes. Then there is a Charts table, and two connected tables: Dx Codes and PCS Codes. The relationships b...
You can use a Conditional Drop Down to limit what is available from a related record. For your case, I would not build new tables. Add the records to the existing table with a new field for the Valid Year and set it to something consistent like Dec 31st every time. Create Formula Date field in your Charts table to give you Dec 31s of the year of the Chart Discharge.
Then make the conditionals in the other tables match the Valid Year Formula Date with the Valid Year.
