Access - Database Logic
Posted on 2007-10-04
I need some confirmation on my database logic.
tblREGION - The ONE table. Includes 2 fields to create a unique key: REGION REGU
tblACCTS - AccountCode. An Account Code can be assigned to many REGION/REGU Combinations and a REGION/REGU can have many Accounts. I'm not sure if this should be a lookup or a relationship table
tblMAIN - Includes the monthly Financial data based on the REGION/REGU/AccountCode . These three fields togeher consitute a unique record.
Region (+ related fields) Regu (+ related fields) Acct (+ related fields) Jan$ Feb$ Mar$
There are no relationships between the tblACCTS and tblREGU until they are joined together in the tblMAIN. Up to now, I updated the tblMAIN fields "REGION", "REGU", "ACCT" with the tblRegion and tblAccts values and I have been working off of the tblMAIN for everthing.
Sometimes, however, some fields in the tblREGU or the tblACCTS may have value changes. Those changes need to reflect in the tblMAIN. So, I now have to rethink the database logic and determine how to set up the relationships between tblREGION, tblACCTs and tblMAIN. Thank you in advance for your suggestions!