SQL 1-to-7 relationship fails ...

[Aad v via AccessMonster.com]

For my work I am creating a kind of 'To do' Database. (SQL/Access adp.)
Every record is an action which is divided by (7) sub-actions.
For this sub-actions people will be assigned as responsible.

Selecting a responsible person will be done by means of comboboxes and a
Lookup table. (tbl_rsp_prsns)
The related persons ID_number will be stored into the main tabel(tbl_main).
So tbl_main has 7 int columns for these ID numbers.

Problem: If I only had one ID_number to store I simply could create an
one_to_many relationship between the tables in my database diagram. But I'm
not sure how to do this for 7 columns.

Help ...

Ronald

 

[Douglas J. Steele]

You shouldn't have 7 fields in the table. Instead, you should have a second
table, with each of the 7 subactions being a separate row in the second
table.

This second table would have the ID from the Action table AND the subaction
number as its primary key.

 

[Aad v via AccessMonster.com]

Do you mean,

-------------------
tbl_subaction
-------------------
sub_action_ID PK
action_ID FK
sub_1 int (4)
sub_2 int (4)
sub_3 int (4)
sub_4 int (4)
sub_5 int (4)
sub_6 int (4)
sub_7 int (4)
-------------------

Or,

-------------------
tbl_subaction
-------------------
sub_action_ID PK
action_ID FK
-------------------

That means that for each record in tbl_main, seven records will be added in
table tbl_subaction. Right?
Can you please explain me how to do that (SP, Trigger ?)

Thanks for help

 

[Douglas J. Steele]

I mean the latter (although you'll also need an additional field to store
the ID of the person responsible for that subaction). Note that
sub_action_ID isn't the PK of the table: it's the combination of bot
sub_action_ID and action_ID.

And technically seven records won't be added in table tbl_subaction each
time you add a record to tbl_main. Rather, you'll add a record to tbl_main,
and that will allow you to add up to seven records to tbl_subaction as you
know who's responsible for each subaction.

Think of it as an invoice. tbl_main represents the invoice itself, while
tbl_subaction represents the individual detail lines on the invoice.

The normal way to do this is using a form/subform setup.

 

[Aad v via AccessMonster.com]

Hi,

Thanks for your time. I'll check that out.

Aad

 

[Aad v via AccessMonster.com]

Hi Douglas,

I have created the Form/ Subform setup.
In the subform there are the 7 comboboxes (one for each sub-action)
They all using tbl_rsp_prsns as Lookup table (set as row source)

I also add a table (tbl_action_items) which contains the 7 sub-actions and
their ID.

Below the existing tables are described (I renamed them).
In the SQL-server diagram window I created the relationships.
The Form and sub-form will be linked by action_ID.

But now I’m not sure how to bind the tables to my Form (and the sub-form)

When a person (rsp_prsns_ID) is selected from one of the comboboxes, how can
identified which combo (action_items_ID) was used.
In other words, which sub-action belongs to the selected person?

------------------------------
tbl_action (main)
------------------------------
action_ID PK
action_date
action_description
etc …
------------------------------

------------------------------
tbl_rsp_prsns
------------------------------
rsp_prsns_ID PK
rsp_prsns_name
rsp_prsns_company
rsp_prsns_initials
------------------------------

------------------------------
tbl_action_items
------------------------------
action_items_ID PK
action_items_description
------------------------------

------------------------------
tbl_rsp_prsns_details
------------------------------
rsp_action_ID FK
rsp_prsns_ID FK
action_items_ID FK
------------------------------

I mean the latter (although you'll also need an additional field to store
the ID of the person responsible for that subaction). Note that
sub_action_ID isn't the PK of the table: it's the combination of bot
sub_action_ID and action_ID.

And technically seven records won't be added in table tbl_subaction each
time you add a record to tbl_main. Rather, you'll add a record to tbl_main,
and that will allow you to add up to seven records to tbl_subaction as you
know who's responsible for each subaction.

Think of it as an invoice. tbl_main represents the invoice itself, while
tbl_subaction represents the individual detail lines on the invoice.

The normal way to do this is using a form/subform setup.

Do you mean,

[quoted text clipped - 27 lines]

Thanks for help