Duplicate Data

[S Jackson]

I have been working on a case mgt database for the past several years. I
started out completely green about MS Access - I knew absolutely nothing! I
would consider my knowledge today to somewhere around the intermediate
level. Recently it has occurred to me that I have some design issues with
the database. I am keeping duplicate data (horror!). Here are the three
culprit tables with some of the relevant fields (I did not include all of
the fields as they are not relevant):

tblCaseInfo
-CaseID
-CaseName
-Region
-FieldOfc
tblSurveyors
-SurveyorID
-SurveyorFirstName
-SurveyorLastName
-SurveyorCity
-SurveyorTitle
-Region
tblCaseSurveyors
-CaseSvyID
-CaseID
-SurveyorID
tblRegion
-ID
-Subofc
-PM
-PMPH
-Ext
-Region

By way of explanation, the relationships are as follows:

one-to-many: tblCaseInfo -> tblCaseSurveyors
one-to-many: tblSurveyors -> tblCaseSurveyors
one-to-one: tblCaseInfo ->tblRegion

tblRegion contains information related to program managers assigned to the
case (a better name for this table would have been tblProgramMgrs to avoid
confusing it with the tblCaseInfo field, Region. Sorry for the confusion).
The information regarding the program managers contained in tblRegion is
also contained in tblSurveyors. This is the design flaw because in order to
update any information related to a program manager (phone, addres, etc.) a
user has to update both tables which is extremely confusing for them.

Currently the query to run my master form to input the information looks
like this:
SELECT tblCaseInfo.*, tblStatus.ClosedDate, tblRegion.ID, tblRegion.SUBOFC,
tblRegion.PM, tblRegion.PMPH, tblRegion.Ext, tblRepresentatives.*
FROM ((tblRepresentatives RIGHT JOIN tblCaseInfo ON tblRepresentatives.ID =
tblCaseInfo.RepID) LEFT JOIN tblRegion ON tblCaseInfo.FieldOfc =
tblRegion.ID) LEFT JOIN tblStatus ON tblCaseInfo.CaseId = tblStatus.CaseId;

On the master form the user selects the tblCaseInfo.Region from a combo box.
This runs an event that populates the FieldOfc combo box with corresponding
entries that match the Region selection:
strSQL = "Select tblRegion.ID, tblRegion.Subofc, tblRegion.PM, " _
& "tblRegion.PMPH, tblRegion.Ext, tblRegion.Region " _
& "FROM tblRegion " _
& "WHERE tblRegion.Region = " & Me.Region & " " _
& "ORDER by tblRegion.Subofc; "
Me.FieldOfc.RowSource = strSQL

The information selected in the FieldOfc combo box is stored in
tblCaseInfo.FieldOfc (the information stored is tblRegion.ID). Once a user
makes a selection from this combo box, the field on the form containing data
for tblRegion.PM, tblRegion.PMPH are automatically populated.

I am having trouble with how to fix all this mess. I do not what to change
how the user selects the program manager. I tried adding this code to the
AfterUpdate event of the Region txtbox:
strSQL = "Select tblSurveyors.SurveyorID, tblSurveyors.SvyCity, " _
& "tblSurveyors.SvyFirstName, tblSurveyors.SvyLastName, " _
& "tblSurveyors.SvyTitle, tblSurveyors.Region, tblSurveyors.SvyPhone " _
& "FROM tblSurveyors " _
& "WHERE tblSurveyors.Region = " & Me.Region & " " _
& "AND tblSurveyors.SvyTitle LIKE " & "'" & "Program*" & "' " _
& "ORDER BY tblSurveyors.svyLastName;"
Me.cmbFieldOfc.RowSource = strSQL
' MsgBox strSQL

It works great to populate the FieldOfc combo box, which I have formated to
display tblSurveyors.SvyCity. There are two problems:

1. When I close and reopen the master form, the FieldOfc combo box is
blank;
2. How can I add txtboxes to display the rest of the information from
tblSurveyors such as tblSurveyors.FirstName, tblSurveyors.LastName (which I
would like to have combined into one text box: tblSurveyors.FirstName & " "
& tblSurveyors.LastName), etc.?

I hope this not too confusing and overwhelming to figure out. Hopefully, I
have given all of the pertinent information.
TIA