Relationship Question

I have three tables with 1:M and M:M relationships:

tblSubSystem
ID (PK)
SubSysName (Text)

tblJoin
SSID
OPID

tblOperation
ID
OpName

A Sub-System can have many Operations and an Operation can be performed on
many Sub-Systems. This works OK. However, I have another table for
Actions:

tblAction
ID (PK)
ActionName (Text)

Each System/Operation combination can have many Actions. How do I achieve
this relationship? I have a hunch that I need to join to both fields in
tblJoin but I can't visualise it. Can anyone help?

Many thanks.
Keith.

Re: Relationship Question

On Oct 31, 8:59 am, "Keith Wilby" wrote:
> I have three tables with 1:M and M:M relationships:
>
> tblSubSystem
> ID (PK)
> SubSysName (Text)
>
> tblJoin
> SSID
> OPID
>
> tblOperation
> ID
> OpName
>
> A Sub-System can have many Operations and an Operation can be performed on
> many Sub-Systems. This works OK. However, I have another table for
> Actions:
>
> tblAction
> ID (PK)
> ActionName (Text)
>
> Each System/Operation combination can have many Actions. How do I achieve
> this relationship? I have a hunch that I need to join to both fields in
> tblJoin but I can't visualise it. Can anyone help?
>
> Many thanks.
> Keith.

If each Sys/Op combination can have many Actions, then just add an
ActionID to tblJoin. And add that new column to your primary key.
Each Sys/Op/Action combination will be unique.

Re: Relationship Question

A quick explanation of relationships may help you solve your issue.

Relationships mainly control the adding and deleting of records (you
should uncheck cascade update/deletes).

If you relate a primary table to a secondary table (the primary table
does not have to be the master table and the secondary table may be the
master table) the primary table will contain unique values.

Example: a primary table contains one column called Parts. This column
is unique in the primary table. The master table also has a parts
column, but the column is not unique. For the sake of the examle say
that the master table contains unique records, but the Parts column just
happens to be not unique (but the sum of all the columns in the master
table will make that record unique). If you try to add a parts value to
the master table that is not first contained in the primary table -- you
will get an error message that you are violating referential integrity
and will not be able to add the Parts value to the Master table until
you have first added that value to the primary table.

And for deletes, lets say that you want to delete a parts value from the
primary table because it is obselete. But the master table contains
some records with that Parts value. Again, you will get an error
message that you are violating referential integrity. You wont be able
to delete the Parts value from the primary table until you have deleted
the parts value from the master table.

Note: if the Parts value can be blank in the Master table, then you
need to include a blank value in the Primary table.

If you have checked cascased update and cascade delete, then you can
delete/add whatever you want. But if you delete a parts value from the
primary table by accident and your master table contains 10,000 rows
with that parts value, they will also be gone -- INSTANTLY. Cascade
Updates/Deletes are primarily for Replication operations (that would be
sql server country). I can't think of real good reason to use cascade
update/deletes in Access.

This is relationships in a nutshell.


Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Relationship Question

On Oct 31, 11:59 am, "Keith Wilby" wrote:
> I have three tables with 1:M and M:M relationships:
>
> tblSubSystem
> ID (PK)
> SubSysName (Text)
>
> tblJoin
> SSID
> OPID
>
> tblOperation
> ID
> OpName
>
> A Sub-System can have many Operations and an Operation can be performed on
> many Sub-Systems. This works OK. However, I have another table for
> Actions:
>
> tblAction
> ID (PK)
> ActionName (Text)
>
> Each System/Operation combination can have many Actions. How do I achieve
> this relationship? I have a hunch that I need to join to both fields in
> tblJoin but I can't visualise it. Can anyone help?
>
> Many thanks.
> Keith.

Just a suggestion here. name your primary key fields uniquely across
tables so that in all tables a gievn field has the same name. In you
Operation table your PK should be OPID just as it is in the Join
table. It makes you code and relationships a LOT easier to read.
Perhaps its personal preference, just tthought I'd throw it out there.

More on a personal preference thing is the use of abbreviations. I
almost never use them. In your Operation table you have a field
called OpName. No harm in calling in OperationName, OperatorName,
OperatingName or whichever it is. It will make things much easier as
your databases get more complex and you start building SQL in code.
It will be even more of a help to the programmer that sits down afetr
you leave...

Re: Relationship Question

"Keith Wilby" schreef in bericht =
news:4728a296$1_1@glkas0286.greenlnk.net...
>I have three tables with 1:M and M:M relationships:
>=20
> tblSubSystem
> ID (PK)
> SubSysName (Text)
>=20
> tblJoin
> SSID
> OPID
>=20
> tblOperation
> ID
> OpName
>=20
> A Sub-System can have many Operations and an Operation can be =
performed on=20
> many Sub-Systems. This works OK. However, I have another table for=20
> Actions:
>=20
> tblAction
> ID (PK)
> ActionName (Text)
>=20
> Each System/Operation combination can have many Actions. How do I =
achieve=20
> this relationship? I have a hunch that I need to join to both fields =
in=20
> tblJoin but I can't visualise it. Can anyone help?
>=20
> Many thanks.
> Keith.=20

Hi Keith=20
I think you could join to both fields indeed in your new table=20

tblSysOpActions
SysOpActionID (PK autonumber)
SSID FK 1-M from tblJoin
OPID FK 1-M from tblJoin =20
ActionID FK 1-M from tblAction
xxxx
xxxx

OR create a new single-field-PK (SysOpID) for your tblJoin (autonumber)
The other two fields would be your secondary key (unique)
Join to this PK

tblSysOpActions
ID (PK Autonumber)
SysOpID (Long) FK 1-M from tblJoin
ActionID FK 1-M from tblAction
xxxx
xxxx

Arno R

Re: Relationship Question

"Arno R" wrote in message
news:4728c36e$0$25473$ba620dc5@text.nova.planet.nl...

Many thanks Arno.

Re: Relationship Question

"Technolust" wrote in message
news:1193849630.730082.145440@q5g2000prf.googlegroups.com...
>
> If each Sys/Op combination can have many Actions, then just add an
> ActionID to tblJoin. And add that new column to your primary key.
> Each Sys/Op/Action combination will be unique.
>
>

Many thanks Technolust.