Dtabase desing and data entry

[Gordon]

I have to design a contacts/relationships database comprising lists of
individuals who can belong to a variety of organsiatiosn/committees,
each in different capacities eg President of 1 committee, Chairmain of
another, simple member of another. I envisage 3 tables:

Table1

fldContactID
fldContactName

Table 2

fldOrganisationID
fldOrganisationName

Table 3

fldPositionID
fldPosition

Given that 1 person can be a member of many organisations and for each
membership he wil be in a different position, I think I need a linking
table 4 comprising:

Table 4

fldOrganisationID
fldContactID
fldPositionID


Am I on the right track here? And if so, how will I be able to define
all those these relationships? I envisage a data entry form where I
can select the person's name (fldContactID) and then choose from
combo boxes which organisation he belongs to and in what capacity.
Will I be able to update all 4 tables in this way? I don't want to go
to all the trouble of populating these tables only to find that it
doesn't work.

Thanks for any help.


Gordon

 

[Klatuu]

This is a pretty ordinary way to do it and in fact is very good relational
design.

You will need to create a query for the record source of your form that will
include Table 1 and Table 4. You can use combo boxes based on Tables 2 and 3
to select the codes necessary to populate the fields in your query.

As another design thought, you can use the Not In List events of the combos
for 2 and 3 to add records as needed to those two tables.

If you need more detail, post back.

 

[Gordon]

This is a pretty ordinary way to do it and in fact is very good relational
design.

You will need to create a query for the record source of your form that will
include Table 1 and Table 4.  You can use combo boxes based on Tables 2 and 3
to select the codes necessary to populate the fields in your query.

As another design thought, you can use the Not In List events of the combos
for 2 and 3 to add records as needed to those two tables.

If you need more detail, post back.
--
Dave Hargis, Microsoft Access MVP








- Show quoted text -

Dave,

Thanks for the confirmation and the advice on the way forward. I'll
get cracking with implementing the design now.


Gordon