There would be no need to change the structure I proposed.Based on this information, would you change the structure we laid out before?
You could just correct the info before you import it into the tables or if you do not want to do that you could set up a table that holds the alternate (but incorrect) project numbers and tie those to the main project table. Each incorrect project number would be a record in this table2. Another complexity in this matter is that some of the full project numbers that come in through my weekly data pull are inaccurate. While they are supposed to conform to the structure I presented to you earlier, there are often setup incorrectly. For this reason, there exists a need to have an overrides table.
The override table would contain the false project number and a corrected number. I'm not sure how I'd include this and connect it to the rest of the data. Does this make sense?
tblAltProjectNumber
-pkAltProjNoID primary key, autonumber
-fkCustDivProProjID foreign key to tblCustomerDivisionProgramProjects
-txtAltProjNo
If you have more than 1 applicant or activity, you would have a one-to-many relationship which would be in a separate but related table3. Also, at the project level I need to assign additional information such activities, applicants, etc. This value would be something I'd find in an Applicants/Activities table that has a finite number of entries. Those tables would be used for looking up values only.
tblCustomerDivisionProgramProjectsActivities
-pkCustDivProgProjActID primary key, autonumber
-fkCustDivProProjID foreign key to tblCustomerDivisionProgramProjects
-fkActivityID foreign key to a table that holds a list of activities
other fields related to the activity for the particular project