Reference tables quandary

Question

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 between them are as follows:

Dx Codes Reference < Dx Codes
Charts < Dx Codes
Pcs Codes Reference < Pcs Codes
Charts < Pcs Codes
MS-DRG Codes < Charts

Each year the Reference tables get an updated codes list. My plan had been to just add 3 new reference tables each year for the new codes list. but my quandary is how to have a field that picks from the appropriate reference table dependent on the chart discharge date. Is there a way of doing this?

Thanks for any insight you can provide.

-Audra

DonLarson · Accepted Answer

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.

DonLarson · Answer

Sign up

Login to the community