Can't Figure Out These Tables

[Shelly Jackson]

I have a table called tblCaseInfo, primary key is a text field, DHSNo

For each case, there are many witnesses. Hence a table called tblWitnesses
which includes a text field, DHSNo.

There is also a table of Surveyors, with a Autonumber, SurveyorID as the
primary key (tblSurveyors)

Surveyors are also "witnesses."

I am trying to build a form that allows the user to enter either Witnesses
or Surveyors (as Witnesses) for each case.

I want the user to be able to pull up a list of "witnesses" for each case
that displays both "witnesses" and "surveyors(also witnesses)."

Absolutely lost here. Do I need a third table called tblWitnessMembers with
three fields: DHSNo, WitnessesID, SurveyorID

Hope I haven't completely confused everyone as much as I apparently seem to
be. Any help is appreciated! I am making everyone around me nuts trying to
figure this out.

Shelly

 

[Gabriela]

If SurvivorTable has a DHSNo then
Yes you have to create a third table called
tblWitnessSurvivors with
three fields: DHSNo, WitnessesID, SurveyorID but the
DHSNo should be a unique "text"
Then you need to create a query that shows all four
tables (include TblCaseInfo and link them:
DHSNo (TblCaseInfo) to DHSNo (Witness Table)
Witness ID (Witness table) to Witness ID (WitnessSurvivor)
Surivor Id (Survivor table) to Survivor ID
(WitnessSurvivor table). This will give you all
witnesses that also show up in the survivor table.
If the survivor table has no DHSNo, is there any field
that is unique and that also shows up in any of the other
tables?
Gabriela

 

[Shelly Jackson]

Thanks Gabriela.

Here is the layout:

tblCaseInfo.DHSNo is the primary key (text field)

tblWitnesses does not have a primary key and has these fields:

DHSNo
Name
Address
City
State
Zip
Ph
Comment
SurveyorID

tblSurveyor has these fields

SurveyorID (AutoNumber field, primary key)
SvyName
SvyTitle
SvyAddress
SvyCity
SvyZip
SvyComment

How do I need to modify these tables, at all?

How do I create a form that a user can select the DHSNo from a drop down
list, input Witnesses (records) to the tblWitnesses for the DHSNo the user
selected, and also select Surveyors from a drop down list that go with the
DHSNo selected? The witnesses and the surveyors need to "assigned" to a
DHSNo.

I'm lost on the basic design of this.

Thanks.

Shelly

 

[GVaught]

tblCaseInfo should have only those fields that pertain to Cases with a
primary key (DHSNo)

tblWitnesses should have only those fields that pertain to Witnesses with a
primary key and the key ((DHSNo) from tblCaseInfo

tblSurveyors should have only those fields that pertain to Surveyors with a
primary key and the key ((DHSNo) from tblCaseInfo

Note: If it is possible that a witness or surveyor can be a member of more
than one case at the same time or later time; then you must create a
bridging table, removing DHSNo from tblWitnesses and tblSurveyors. Then
creating two new tables called tblCaseSurveyorMembers containing: its own
primary key(autonumber), DHSNO, and SurveryorID and tblCaseWitnessMembers
containing: its own primary key(autonumber), DHSNO, and WitnessId.

Note: tblSurveyors and tblWitnesses could be combined as one table if the
data collected is the nearly the same. What you would add is a field to
define the their MemberType; Witness or Surveyor. This can be a separate
table or you could develop a combo box value list within your form.

Note if you combined Witness and Surveryor to one table and add a field
called MemberType (Witness/Surveyor), then you only need one new table
above.

As for the form. The main form should be based on tblCaseInfo; the first
subform to the main form should be tblWitnesses and the second subform to
the main form should be tblSurveyors. Note if you combine Witnesses and
Surveryor, then you will only have one subform to the main form.

Note: Depending on how you develop your tables, will depend on how you
create your main forms and subforms.

Hope that helps.