multiple relationships between 2 tables

Hello and thanks for looking into my question,

I have a table of servers which is related to a table of server services via
the server name field (pid). This is a 1 to 1 relationship because for each
server in one table, there can only be one entry for it's services in the
other table. This relationships works great. For clarification, here's my
setup below.

tbl_ServerName (1st table)
------------------
serverName (pid)

tbl_ServerServices (2nd table)
---------------------
serverName (pid) (linked to my 1st table via a 1-to-1)
serverService1
serverService2
serverService3

Ok, now my serverService1 field in the 2nd table above is linked to a third
table listing all of the server services. This is a one to many relationship.
It works great too. Here is the what the 3rd table looks like:

tbl_Service (3rd table)
------------
service (pid) (now linked to serverService1 field in 2nd table via 1-to-many.)

My question is that I would also like to link my serverService2 and
serverService3 fields above to the same third table. This way, if I make any
changes to any of the server services in the third table, it also shows up in
the 3 fields on my 2nd table.

Is this even possible? Please advise.

Thank you in advance.

-Blenvid

Stop...
Consider creating a table of server services like:

tbl_ServerServices
----------------------------
serverName
service

A record is created for each service on each server. If a server has 20
services, there should be 20 records.

--
Duane Hookom
MS Access MVP


"Blenvid" wrote in message
...
Hello and thanks for looking into my question,

I have a table of servers which is related to a table of server services
via
the server name field (pid). This is a 1 to 1 relationship because for
each
server in one table, there can only be one entry for it's services in the
other table. This relationships works great. For clarification, here's my
setup below.

tbl_ServerName (1st table)
------------------
serverName (pid)

tbl_ServerServices (2nd table)
---------------------
serverName (pid) (linked to my 1st table via a 1-to-1)
serverService1
serverService2
serverService3

Ok, now my serverService1 field in the 2nd table above is linked to a
third
table listing all of the server services. This is a one to many
relationship.
It works great too. Here is the what the 3rd table looks like:

tbl_Service (3rd table)
------------
service (pid) (now linked to serverService1 field in 2nd table via
1-to-many.)

My question is that I would also like to link my serverService2 and
serverService3 fields above to the same third table. This way, if I make
any
changes to any of the server services in the third table, it also shows up
in
the 3 fields on my 2nd table.

Is this even possible? Please advise.

Thank you in advance.

-Blenvid

Hi Duane,

Thanks for responding to my question.

That would solve the problem. This would create some work for me though;
I've got 173 servers, some with 3 services each.

Is the way that I'm trying to do it incorrect?

-Blenvid

"Duane Hookom" wrote:

Stop...
Consider creating a table of server services like:

tbl_ServerServices
----------------------------
serverName
service

A record is created for each service on each server. If a server has 20
services, there should be 20 records.

--
Duane Hookom
MS Access MVP


"Blenvid" wrote in message
...
Hello and thanks for looking into my question,

I have a table of servers which is related to a table of server services
via
the server name field (pid). This is a 1 to 1 relationship because for
each
server in one table, there can only be one entry for it's services in the
other table. This relationships works great. For clarification, here's my
setup below.

tbl_ServerName (1st table)
------------------
serverName (pid)

tbl_ServerServices (2nd table)
---------------------
serverName (pid) (linked to my 1st table via a 1-to-1)
serverService1
serverService2
serverService3

Ok, now my serverService1 field in the 2nd table above is linked to a
third
table listing all of the server services. This is a one to many
relationship.
It works great too. Here is the what the 3rd table looks like:

tbl_Service (3rd table)
------------
service (pid) (now linked to serverService1 field in 2nd table via
1-to-many.)

My question is that I would also like to link my serverService2 and
serverService3 fields above to the same third table. This way, if I make
any
changes to any of the server services in the third table, it also shows up
in
the 3 fields on my 2nd table.

Is this even possible? Please advise.

Thank you in advance.

-Blenvid

Blenvid wrote:

Hello and thanks for looking into my question,

I have a table of servers which is related to a table of server services via
the server name field (pid). This is a 1 to 1 relationship because for each
server in one table, there can only be one entry for it's services in the
other table. This relationships works great. For clarification, here's my
setup below.

tbl_ServerName (1st table)
------------------
serverName (pid)

tbl_ServerServices (2nd table)
---------------------
serverName (pid) (linked to my 1st table via a 1-to-1)
serverService1
serverService2
serverService3

I get the impression that [serverService2] is the same kind of beast as
[serverService1]. If so, I suggest that they share ONE field in some
Table, instead of occupying separate fields. Access will work with them
in separate fields, but you'll make extra work for yourself doing it
that way.

Ok, now my serverService1 field in the 2nd table above is linked to a third
table listing all of the server services.

Linked how? I assume it somehow contains a reference to the primary key
(not shown) in your [tbl_Service] Table. But if so, it wouldn't be
one-to-many, as one field can't contain "many" values.

What I think you want is many services linked to one server; i.e., many
records in [tbl_Service] that contain a reference to a
[tbl_ServerName].[serverName] key value.

This is a one to many relationship.
It works great too. Here is the what the 3rd table looks like:

tbl_Service (3rd table)
------------
service (pid) (now linked to serverService1 field in 2nd table via 1-to-many.)

"1-to-many" suggests that for each service (represented by a record in
[tbl_Service]) you can have several servers (represented by records in
[tbl_ServerName]) that share it. I assume you have other fields in
[tbl_Service] as well, containing useful information about the actual
service. The [tbl_Service].[service] field serves mainly to identify a
record and I assume does little else, although you could legitimately
have it do double duty, using a unique name for the service that means
something to human beings. But I usually prefer a meaningless key, such
as an Autonumber field, that won't have to change if you change some
other field in a record. If you used a real name there, and you
discover that it's misspelled, you'd have to change it not only there
but also everywhere else in the database that it appeared; there'd be no
reason to change an Autonumber key value.

Since [tbl_ServerServices] can associate three [tbl_Service] records
with one [tbl_ServerName] record, this looks like a "many-to-many"
relationship. That will work, but it requires an extra Table in which
each record associates one server with one of the services, and maybe
also tells which of the 3 services that is.

For example, you could revise the design of your 2nd table to look like
this:

[tbl_ServerServices] (revised 2nd table)

[serverName] (link to 1st Table, via many-to-1)
[service] (link to 3rd Table, via many-to-1)
[Number] (=1, 2, or 3, to specify which service this is)

With all 3 Tables linked, you now have a many-to-many relationship
defined that (I think) reflects what you want to keep track of.

My question is that I would also like to link my serverService2 and
serverService3 fields above to the same third table. This way, if I make any
changes to any of the server services in the third table, it also shows up in
the 3 fields on my 2nd table.

With the design I suggested, your change will show up in any record
(which you'd display via a Query) in which some server uses that service.

Is this even possible? Please advise.

Thank you in advance.

-Blenvid

HTH.

-- Vincent Johns
Please feel free to quote anything I say here.

Blenvid wrote:

Hi Duane,

Thanks for responding to my question.

That would solve the problem. This would create some work for me though;
I've got 173 servers, some with 3 services each.

Is the way that I'm trying to do it incorrect?

-Blenvid

Yes, there'll be some work, but it's a one-time project; you won't have
to redo it.

If you had thousands of servers, I'd suggest writing Append Queries to
copy the information to [tbl_ServerServices]. You'd do that 3 times,
once for each of the 3 values.

But with only 173 records, you might find it easier to copy your current
[tbl_ServerServices] Table, let's say to [tbl_New], rename the
[serverService1] field in the copy to [Service], and delete the other 2
fields. Also change the [serverName] field so that it doesn't have to
be unique. That takes care of [serverService1].

Now make another copy of [tbl_ServerServices], let's say to Table [t2],
and this time delete [serverService1] and [serverService3] and make
[serverName] not unique. Rename the fields in [t2] to match the field
names in [tbl_New]. In the Database window, copy this Table and paste
it; select "Append Data to Existing Table", and specify "tbl_New" as the
Table Name. If you get an error message, correct the mistake and try
again; otherwise, the data are appended and you can delete table [t2].

Now make another copy of [tbl_ServerServices], let's say to Table [t3]
and do the same as with [t2] but keeping [serverService3] this time.

Now [tbl_New] contains all the records you need. Sort it by its
[Service] field and delete the records where that field is blank.
Rename or delete the old [tbl_ServerServices] Table, and rename
[tbl_New] to call it "tbl_ServerServices". You may also want to add a
primary key to it (I'd suggest an Autonumber field), but that may not be
strictly necessary.

Link the fields in the new [tbl_ServerServices] Table to the other 2
Tables, using the Relationships window, and you're in business.

-- Vincent Johns
Please feel free to quote anything I say here.


"Duane Hookom" wrote:


Stop...
Consider creating a table of server services like:

tbl_ServerServices
----------------------------
serverName
service

A record is created for each service on each server. If a server has 20
services, there should be 20 records.

--
Duane Hookom
MS Access MVP

Vincent Johns wrote:

[...]
If you had thousands of servers, I'd suggest writing Append Queries to
copy the information to [tbl_ServerServices]. You'd do that 3 times,
once for each of the 3 values.

But with only 173 records, you might find it easier to copy your current
[tbl_ServerServices] Table, ...

Oops -- I think I forgot to mention that it's an excellent idea to make
a BACKUP COPY of your database file before doing anything like this!
(As well as on many other occasions, such as before thunderstorms and
hard-disk crashes.)

-- Vincent Johns
Please feel free to quote anything I say here.

Hi Vincent,

Thank you also for your reply.

Please note, my 3rd table looks like this:

tbl_Service (3rd table)
------------
service (pid) (now linked to serverService1 field in 2nd table via 1-to-many.)

I chose to go with the service field as my PID because each services name in
itself is descriptive. There are other fields, but I don't want to make
things seemingly more complex by including them.

For each original record in [tbl_Service.service] above, there are many
records in [tbl_ServerServices.serverService1]. In my relationships view in
Access, this is shown of course as 1 on the [tbl_Service.service] side, and 8
sideways on the [tbl_ServerServices] side.

I am a little confused by your response. Please clarify your comment, "Since
[tbl_ServerServices] can associate three [tbl_Service] records with one
[tbl_ServerName] record, this looks like a "many-to-many" relationship."

You are right on the money here Vincent:

" What I think you want is many services linked to one server; i.e., many
records in [tbl_Service] that contain a reference to a
[tbl_ServerName].[serverName] key value."

This is exactly what I want.

-Blenvid


"Vincent Johns" wrote:

Blenvid wrote:

Hello and thanks for looking into my question,

I have a table of servers which is related to a table of server services via
the server name field (pid). This is a 1 to 1 relationship because for each
server in one table, there can only be one entry for it's services in the
other table. This relationships works great. For clarification, here's my
setup below.

tbl_ServerName (1st table)
------------------
serverName (pid)

tbl_ServerServices (2nd table)
---------------------
serverName (pid) (linked to my 1st table via a 1-to-1)
serverService1
serverService2
serverService3

I get the impression that [serverService2] is the same kind of beast as
[serverService1]. If so, I suggest that they share ONE field in some
Table, instead of occupying separate fields. Access will work with them
in separate fields, but you'll make extra work for yourself doing it
that way.

Ok, now my serverService1 field in the 2nd table above is linked to a third
table listing all of the server services.

Linked how? I assume it somehow contains a reference to the primary key
(not shown) in your [tbl_Service] Table. But if so, it wouldn't be
one-to-many, as one field can't contain "many" values.

What I think you want is many services linked to one server; i.e., many
records in [tbl_Service] that contain a reference to a
[tbl_ServerName].[serverName] key value.

This is a one to many relationship.
It works great too. Here is the what the 3rd table looks like:

tbl_Service (3rd table)
------------
service (pid) (now linked to serverService1 field in 2nd table via 1-to-many.)

"1-to-many" suggests that for each service (represented by a record in
[tbl_Service]) you can have several servers (represented by records in
[tbl_ServerName]) that share it. I assume you have other fields in
[tbl_Service] as well, containing useful information about the actual
service. The [tbl_Service].[service] field serves mainly to identify a
record and I assume does little else, although you could legitimately
have it do double duty, using a unique name for the service that means
something to human beings. But I usually prefer a meaningless key, such
as an Autonumber field, that won't have to change if you change some
other field in a record. If you used a real name there, and you
discover that it's misspelled, you'd have to change it not only there
but also everywhere else in the database that it appeared; there'd be no
reason to change an Autonumber key value.

Since [tbl_ServerServices] can associate three [tbl_Service] records
with one [tbl_ServerName] record, this looks like a "many-to-many"
relationship. That will work, but it requires an extra Table in which
each record associates one server with one of the services, and maybe
also tells which of the 3 services that is.

For example, you could revise the design of your 2nd table to look like
this:

[tbl_ServerServices] (revised 2nd table)

[serverName] (link to 1st Table, via many-to-1)
[service] (link to 3rd Table, via many-to-1)
[Number] (=1, 2, or 3, to specify which service this is)

With all 3 Tables linked, you now have a many-to-many relationship
defined that (I think) reflects what you want to keep track of.

My question is that I would also like to link my serverService2 and
serverService3 fields above to the same third table. This way, if I make any
changes to any of the server services in the third table, it also shows up in
the 3 fields on my 2nd table.

With the design I suggested, your change will show up in any record
(which you'd display via a Query) in which some server uses that service.

Is this even possible? Please advise.

Thank you in advance.

-Blenvid

HTH.

-- Vincent Johns
Please feel free to quote anything I say here.

Blenvid wrote:

Hi Vincent,

Thank you also for your reply.

Please note, my 3rd table looks like this:

tbl_Service (3rd table)
------------
service (pid) (now linked to serverService1 field in 2nd table via 1-to-many.)

I chose to go with the service field as my PID because each services name in
itself is descriptive.

OK. But if it's a long name, and the same name is used often, you might
save space by storing the names somewhere else and linking to them with
a number field. Also, if you ever need to change the name, you'll have
to make the same change everywhere it appears in every other Table
linking to this one.

There are other fields, but I don't want to make
things seemingly more complex by including them.

That's what I thought, but sometimes a Table can be useful even if it
contains only one or two fields, so I wasn't sure.

For each original record in [tbl_Service.service] above, there are many
records in [tbl_ServerServices.serverService1]. In my relationships view in
Access, this is shown of course as 1 on the [tbl_Service.service] side, and 8
sideways on the [tbl_ServerServices] side.

But that's only to the first field, [tbl_ServerServices.serverService1],
isn't it? Having linked the record via that field, I'm not sure what it
would mean to try to also link it via some other field, such as
[tbl_ServerServices.serverService3].

(Incidentally, the "oo" symbol means "infinity" or "endlessness" in
mathematics; here it just means we don't care where it ends, it just
doesn't have to end with 1 record.)

I am a little confused by your response. Please clarify your comment, "Since
[tbl_ServerServices] can associate three [tbl_Service] records with one
[tbl_ServerName] record, this looks like a "many-to-many" relationship."

That was my guess, based on what you'd said earlier. The statement by
itself isn't sufficient to generate that "many-to-many" conclusion. But
it also looked to me as if one service ([tbl_Service] record) might be
referred to by more than one server.

If you know that that will never be the case, then you do have 1
(server) to many (services), and you don't need the third Table. In
that case, you'd just store a server reference into each record in
[tbl_Service] instead of what I suggested. There'd be no need for the
2nd Table. The other field I suggested, [Number], showing that a
service is number 1, 2, or 3 in your current [tbl_ServerServices] list,
you could store in the [tbl_Service] Table, if you needed to keep track
of that.

You are right on the money here Vincent:

" What I think you want is many services linked to one server; i.e., many

records in [tbl_Service] that contain a reference to a
[tbl_ServerName].[serverName] key value."

This is exactly what I want.

-Blenvid

-- Vincent Johns
Please feel free to quote anything I say here.

Thanks so much for your continued support here Vincent. You're spending some
good time on my situation and I really appreciate it.

Actually, one of the benefits of relationships is that if you change a
name/field in one place, it will update the name/field in related tables?
That's really my biggest interest in doing all of this, although I doubt
someone will change the entry, "DNS," in the future. (Maybe someday there'll
be a DNS v.2, like IP addressing ex. ipv6) The names are quite short too. The
longest one is "Active Directory."

Anyway, your paragraph he

But that's only to the first field, [tbl_ServerServices.serverService1],
isn't it? Having linked the record via that field, I'm not sure what it
would mean to try to also link it via some other field, such as
[tbl_ServerServices.serverService3].

I actually have tried this. I can link all 3
[tbl_ServerServices.serverServiceX] fields via a relationship, but it's not
with Referential Integrity, the whole reason for linking them in the first
place. In the "Edit relationships" prompt, you can select multiple fields. I
selected [tbl_Service.service] as my source field, then selected the 3 fields
of serverServices 1, 2, and 3. I get an error message with Referential
Integrity checked. Here is the error message:

"Microsoft Access can't enforce referential intergrity for this
relationship. Make sure the fields you drag are primary key fields or
uniquely indexed and that the unique index or primary key is correctly set.
If you want to create the relationship without the rules of referential
integrity, clear the Enforce Referential Integrity check box."

I need some time to look at this paragraph:

If you know that that will never be the case, then you do have 1
(server) to many (services), and you don't need the third Table. In
that case, you'd just store a server reference into each record in
[tbl_Service] instead of what I suggested. There'd be no need for the
2nd Table. The other field I suggested, [Number], showing that a
service is number 1, 2, or 3 in your current [tbl_ServerServices] list,
you could store in the [tbl_Service] Table, if you needed to keep track
of that.

This may be the answer. Do you mean that I should create a number field in
[tbl_Service] for each service? Then in the [tbl_ServerServices] table, use
the number to distinguish between services? If this is the answer, how would
I then be able to link them with Referential Integrity?

-Blenvid

Vincent Johns wrote:
service field as my PID because each services name in
itself is descriptive.

if you ever need to change the name, you'll have
to make the same change everywhere it appears in every other Table
linking to this one.

Choose ON UPDATE CASCADE on the FOREIGN KEY constraints and the system
does for you.