many-To-Many from entering only once in the form??

[qajussi]

I have one table which draws relationships.
Here is an example.

TblStaff
StaffID
LastName

TblAffiliatedStaff
StaffID
AffiliationType
AffilStaffID1
AffilStaffID2
AffilStaffID3
AffilStaffID4

===================
AffilStaffID is the StaffID.
TblAffiliatedStaff draws links between StaffID to other staff as 1-to-Many relationship.
What I want to do it draw many-to-many relationships.
ex)
I have a form for TblAffiliatedStaff.
I will enter:
Fm_AffiliatedStaff
staff7
Research Type
staff3
staff14
staff21
staff66

Which in turn draw relationships like these.
And insert them into the TblAffiliatedStaff.
staff7 -> staff3
staff7 -> staff14
staff7 -> staff21
staff7 -> staff66


What I want is:

staff7 -> staff3
staff7 -> staff14
staff7 -> staff21
staff7 -> staff66

staff3 -> staff7
staff3 -> staff14
staff3 -> staff21
staff3 -> staff66

staff14 -> staff7
staff14 -> staff3
staff14 -> staff21
staff14 -> staff66

staff21 -> staff7
staff21 -> staff3
staff21 -> staff14
staff21 -> staff66

staff66 -> staff7
staff66 -> staff14
staff66 -> staff21
staff66 -> staff3
And insert them into the TblAffiliatedStaff.
Of course I want to enter only once like this from the form.
Fm_AffiliatedStaff
staff7
Research Type
staff3
staff14
staff21
staff66

How can I make these automatically draw relationship and insert them into the table??
Thank you so much in advance.

 

[lespaul]

Before you start inserting data into this table you should read up on Normalization:

'The Fundamentals of Relational Database Design'


Leslie

 

[qajussi]

Thanks Leslie.

I am creating a data entry forms for my analysts to enter their research information.
They have to enter so many personnel information(hundreds) and they asked me to make a form to allow them to enter maximum of 10 staff afiiliations at a time.
I don't know any other way to do this other than making 10 entries.

Do you know a better way to do accomodate this??
Thanks again for your input.

TblAffiliatedStaff
StaffID
AffiliationType
AffilStaffID1
AffilStaffID2
AffilStaffID3
AffilStaffID4

 

[dhookom]

Data structure should not be determined by user interface. You can create a form that loads and unloads data from your normalized table.

If you need help with presenting normalized data in a "spreadsheet" view then ask...

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]