By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,835 Members | 1,391 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,835 IT Pros & Developers. It's quick & easy.

The ONE scenario never addressed in books

P: n/a
Max
One of the most common situations I keep finding myself in when
creating or working on an MS Access db is figuring out how to relate a
table that actually has a place in more than 1 tiered one-to-many
related tables. Let me explain: The easiest example is a phone
number in a multi-level organized company...

Lets say you have a main Regional Office. Under that Regional Office,
you have about 5 or 6 Area Offices that are all considered part of
that Regional Office. Under each Area Office, there are about 12-15
Branch Offices that are all considered Part of that Area Office. And
under each Branch Office, there would be Employees all considered to
be under that Branch. This would make tables with the following
relationship to each other:

[Regional Office]-OneToMany-[Area Office]-OneToMany-[Branch
Office]--OneToMany--[Employees]

On the lowest level [Employees] you could make a [Phone#] field. You
would think this would be enough, since a phone number should be
associated at an employee level. But what if (as the case actually
is) there is a phone number specifically for a Branch as a whole; a
main phone number for the branch. And, there is a specific phone
number for each Area Office...and a specific phone number for the
Regional office.

Obviously, it would be a good idea to make a separate [Phones] table.
But here then is the question: How do you relate it to the tiered
tables? What is the most "normalized" way to do that?

MS Access books never seem to address this particular scenario, even
though I see it come up situationally all the time.
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
You can have a phone table, with phone id and phone number. Then you can
have foreign keys in multiple of the other tables that point to entries in
the phones table. If each person/branch can have multiple phones those would
each be in a many-to-many relationship with phones, requiring a junction
(aka intersection) table. If each can only have one, then it is a
one-to-many relationship from phones to the other tables. Sure if you have
some honkin' big query that pulls down multiple levels of informationk, you
may have to have the phones table in that query multiple times.

Larry Linson
Microsoft Access MVP
"Max" <ma******@msn.com> wrote in message
news:4f**************************@posting.google.c om...
One of the most common situations I keep finding myself in when
creating or working on an MS Access db is figuring out how to relate a
table that actually has a place in more than 1 tiered one-to-many
related tables. Let me explain: The easiest example is a phone
number in a multi-level organized company...

Lets say you have a main Regional Office. Under that Regional Office,
you have about 5 or 6 Area Offices that are all considered part of
that Regional Office. Under each Area Office, there are about 12-15
Branch Offices that are all considered Part of that Area Office. And
under each Branch Office, there would be Employees all considered to
be under that Branch. This would make tables with the following
relationship to each other:

[Regional Office]-OneToMany-[Area Office]-OneToMany-[Branch
Office]--OneToMany--[Employees]

On the lowest level [Employees] you could make a [Phone#] field. You
would think this would be enough, since a phone number should be
associated at an employee level. But what if (as the case actually
is) there is a phone number specifically for a Branch as a whole; a
main phone number for the branch. And, there is a specific phone
number for each Area Office...and a specific phone number for the
Regional office.

Obviously, it would be a good idea to make a separate [Phones] table.
But here then is the question: How do you relate it to the tiered
tables? What is the most "normalized" way to do that?

MS Access books never seem to address this particular scenario, even
though I see it come up situationally all the time.

Nov 12 '05 #2

P: n/a
The point that you are missing is that when you create a table, you add fields
to that table to record all the attributes about the subject of that table. In
your example, considering the subject of a table is Area Office, one attribute
is that each Area Office is subordinate to the Regional Office so you need
RegionalOfficeID as a field in the Area Office table to record that attribute.
Another attribute of each Area Office is that each has a main telephone number
so you need AreaOfficeTeleNum as a field in the Area Office table to record that
attribute. Your Area Office table then looks like:

TblAreaOffice
AreaOfficeID
RegionalOfficeID
AreaOfficeTeleNum

Following the same logic, your Branch table looks like:

TblBranch
BranchID
AreaOfficeID
BranchTeleNum

So when you say "Obviously, it would be a good idea to make a separate [Phones]
table." That statement is incorrect in regards to the design of the tables you
need.
"Max" <ma******@msn.com> wrote in message
news:4f**************************@posting.google.c om...
One of the most common situations I keep finding myself in when
creating or working on an MS Access db is figuring out how to relate a
table that actually has a place in more than 1 tiered one-to-many
related tables. Let me explain: The easiest example is a phone
number in a multi-level organized company...

Lets say you have a main Regional Office. Under that Regional Office,
you have about 5 or 6 Area Offices that are all considered part of
that Regional Office. Under each Area Office, there are about 12-15
Branch Offices that are all considered Part of that Area Office. And
under each Branch Office, there would be Employees all considered to
be under that Branch. This would make tables with the following
relationship to each other:

[Regional Office]-OneToMany-[Area Office]-OneToMany-[Branch
Office]--OneToMany--[Employees]

On the lowest level [Employees] you could make a [Phone#] field. You
would think this would be enough, since a phone number should be
associated at an employee level. But what if (as the case actually
is) there is a phone number specifically for a Branch as a whole; a
main phone number for the branch. And, there is a specific phone
number for each Area Office...and a specific phone number for the
Regional office.

Obviously, it would be a good idea to make a separate [Phones] table.
But here then is the question: How do you relate it to the tiered
tables? What is the most "normalized" way to do that?

MS Access books never seem to address this particular scenario, even
though I see it come up situationally all the time.

Nov 12 '05 #3

P: n/a
Max,
It looks to me like your data structure is wrong from the start.

What if the business structure changes? Say a layer of administration is
slipped in between Region and Area, or say Area is removed? You would have
to restructure your database to represent this.

What you have sounds like a classic employees table where there is a
requirement to show a hierarchy.

You can create a table for the offices with all the details for that office
(including the phone number), you can then add a field to the table which
indicates which office the particular office in the record reports to (this
is like a foreign key to the same table).

You can then link the table to itself in a query in order to represent the
hierarchy (link from the primary key to the internal foreign key)

Let's show a simple example a table called tblOffices

officeId | parentid | office_name | phone
-------------------------------------------
1 | 0 |head |123456
2 | 1 |region1 |1654321
3 | 1 |region2 |963258
4 | 2 |area 1 1 |58214
5 | 2 |area 1 2 |96325
6 | 2 |area 1 3 |987456
7 | 3 |area 2 1 |87945
8 | 3 |area 2 2 |21345
Where
Field Name Data Type Note
------------------------------------------
officeId Autonumber Primary Key
parentid Long
office_name Text
phone Text
You can then create a query like this

SELECT tO1.office_name, tO1.phone, tO2.office_name, tO2.phone,
tO3.office_name, tO3.phone
FROM (tblOffices AS tO1 INNER JOIN tblOffices AS tO2 ON tO1.officeId =
tO2.parentid) INNER JOIN tblOffices AS tO3 ON tO2.officeId = tO3.parentid;

Which would produce the following results

tO1.office_name | tO1.phone | tO2.office_name | tO2.phone | tO3.office_name
| tO3.phone
----------------------------------------------------------------------------
------------
head | 123456 | region1 | 654321 | area 1 1
| 58214
head | 123456 | region1 | 654321 | area 1 2
| 96325
head | 123456 | region1 | 654321 | area 1 3
| 987456
head | 123456 | region2 | 963258 | area 2 1
| 87945
head | 123456 | region2 | 963258 | area 2 2
| 21345
The advantage of this structure is that if the organisation structure
changes then the data changes, not the structure of the database.

Terry
"Max" <ma******@msn.com> wrote in message
news:4f**************************@posting.google.c om...
One of the most common situations I keep finding myself in when
creating or working on an MS Access db is figuring out how to relate a
table that actually has a place in more than 1 tiered one-to-many
related tables. Let me explain: The easiest example is a phone
number in a multi-level organized company...

Lets say you have a main Regional Office. Under that Regional Office,
you have about 5 or 6 Area Offices that are all considered part of
that Regional Office. Under each Area Office, there are about 12-15
Branch Offices that are all considered Part of that Area Office. And
under each Branch Office, there would be Employees all considered to
be under that Branch. This would make tables with the following
relationship to each other:

[Regional Office]-OneToMany-[Area Office]-OneToMany-[Branch
Office]--OneToMany--[Employees]

On the lowest level [Employees] you could make a [Phone#] field. You
would think this would be enough, since a phone number should be
associated at an employee level. But what if (as the case actually
is) there is a phone number specifically for a Branch as a whole; a
main phone number for the branch. And, there is a specific phone
number for each Area Office...and a specific phone number for the
Regional office.

Obviously, it would be a good idea to make a separate [Phones] table.
But here then is the question: How do you relate it to the tiered
tables? What is the most "normalized" way to do that?

MS Access books never seem to address this particular scenario, even
though I see it come up situationally all the time.

Nov 12 '05 #4

P: n/a
Hi Terry,
I downloaded your SearchRecords2k from Dev's site. it is really neat.
Have you made any changes to it?
I want to add the ability to search linked SQL server tables. I just began
playing with your code
nd the linked tables appear in the list box but I could not get the rest to
work - I had to run before I could do any more work.
If you have any suggestions, they would be most welcome, and I'd be happy to
share the finished product with you.

Regards
Habib

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:bl**********@newsreaderg1.core.theplanet.net. ..
Max,
It looks to me like your data structure is wrong from the start.

What if the business structure changes? Say a layer of administration is
slipped in between Region and Area, or say Area is removed? You would have
to restructure your database to represent this.

What you have sounds like a classic employees table where there is a
requirement to show a hierarchy.

You can create a table for the offices with all the details for that office (including the phone number), you can then add a field to the table which
indicates which office the particular office in the record reports to (this is like a foreign key to the same table).

You can then link the table to itself in a query in order to represent the
hierarchy (link from the primary key to the internal foreign key)

Let's show a simple example a table called tblOffices

officeId | parentid | office_name | phone
-------------------------------------------
1 | 0 |head |123456
2 | 1 |region1 |1654321
3 | 1 |region2 |963258
4 | 2 |area 1 1 |58214
5 | 2 |area 1 2 |96325
6 | 2 |area 1 3 |987456
7 | 3 |area 2 1 |87945
8 | 3 |area 2 2 |21345
Where
Field Name Data Type Note
------------------------------------------
officeId Autonumber Primary Key
parentid Long
office_name Text
phone Text
You can then create a query like this

SELECT tO1.office_name, tO1.phone, tO2.office_name, tO2.phone,
tO3.office_name, tO3.phone
FROM (tblOffices AS tO1 INNER JOIN tblOffices AS tO2 ON tO1.officeId =
tO2.parentid) INNER JOIN tblOffices AS tO3 ON tO2.officeId = tO3.parentid;

Which would produce the following results

tO1.office_name | tO1.phone | tO2.office_name | tO2.phone | tO3.office_name | tO3.phone
-------------------------------------------------------------------------- -- ------------
head | 123456 | region1 | 654321 | area 1 1
| 58214
head | 123456 | region1 | 654321 | area 1 2
| 96325
head | 123456 | region1 | 654321 | area 1 3
| 987456
head | 123456 | region2 | 963258 | area 2 1
| 87945
head | 123456 | region2 | 963258 | area 2 2
| 21345
The advantage of this structure is that if the organisation structure
changes then the data changes, not the structure of the database.

Terry
"Max" <ma******@msn.com> wrote in message
news:4f**************************@posting.google.c om...
One of the most common situations I keep finding myself in when
creating or working on an MS Access db is figuring out how to relate a
table that actually has a place in more than 1 tiered one-to-many
related tables. Let me explain: The easiest example is a phone
number in a multi-level organized company...

Lets say you have a main Regional Office. Under that Regional Office,
you have about 5 or 6 Area Offices that are all considered part of
that Regional Office. Under each Area Office, there are about 12-15
Branch Offices that are all considered Part of that Area Office. And
under each Branch Office, there would be Employees all considered to
be under that Branch. This would make tables with the following
relationship to each other:

[Regional Office]-OneToMany-[Area Office]-OneToMany-[Branch
Office]--OneToMany--[Employees]

On the lowest level [Employees] you could make a [Phone#] field. You
would think this would be enough, since a phone number should be
associated at an employee level. But what if (as the case actually
is) there is a phone number specifically for a Branch as a whole; a
main phone number for the branch. And, there is a specific phone
number for each Area Office...and a specific phone number for the
Regional office.

Obviously, it would be a good idea to make a separate [Phones] table.
But here then is the question: How do you relate it to the tiered
tables? What is the most "normalized" way to do that?

MS Access books never seem to address this particular scenario, even
though I see it come up situationally all the time.


Nov 12 '05 #5

P: n/a
That piece of software was originally written by Dev.

Terry
"HSalim" <On******************@msn.com> wrote in message
news:FQ*****************@nwrdny03.gnilink.net...
Hi Terry,
I downloaded your SearchRecords2k from Dev's site. it is really neat.
Have you made any changes to it?
I want to add the ability to search linked SQL server tables. I just began playing with your code
nd the linked tables appear in the list box but I could not get the rest to work - I had to run before I could do any more work.
If you have any suggestions, they would be most welcome, and I'd be happy to share the finished product with you.

Regards
Habib

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:bl**********@newsreaderg1.core.theplanet.net. ..
Max,
It looks to me like your data structure is wrong from the start.

What if the business structure changes? Say a layer of administration is
slipped in between Region and Area, or say Area is removed? You would have to restructure your database to represent this.

What you have sounds like a classic employees table where there is a
requirement to show a hierarchy.

You can create a table for the offices with all the details for that

office
(including the phone number), you can then add a field to the table which indicates which office the particular office in the record reports to

(this
is like a foreign key to the same table).

You can then link the table to itself in a query in order to represent the hierarchy (link from the primary key to the internal foreign key)

Let's show a simple example a table called tblOffices

officeId | parentid | office_name | phone
-------------------------------------------
1 | 0 |head |123456
2 | 1 |region1 |1654321
3 | 1 |region2 |963258
4 | 2 |area 1 1 |58214
5 | 2 |area 1 2 |96325
6 | 2 |area 1 3 |987456
7 | 3 |area 2 1 |87945
8 | 3 |area 2 2 |21345
Where
Field Name Data Type Note
------------------------------------------
officeId Autonumber Primary Key
parentid Long
office_name Text
phone Text
You can then create a query like this

SELECT tO1.office_name, tO1.phone, tO2.office_name, tO2.phone,
tO3.office_name, tO3.phone
FROM (tblOffices AS tO1 INNER JOIN tblOffices AS tO2 ON tO1.officeId =
tO2.parentid) INNER JOIN tblOffices AS tO3 ON tO2.officeId = tO3.parentid;
Which would produce the following results

tO1.office_name | tO1.phone | tO2.office_name | tO2.phone |

tO3.office_name
| tO3.phone


--------------------------------------------------------------------------
--
------------
head | 123456 | region1 | 654321 | area 1 1
| 58214
head | 123456 | region1 | 654321 | area 1 2
| 96325
head | 123456 | region1 | 654321 | area 1 3
| 987456
head | 123456 | region2 | 963258 | area 2 1
| 87945
head | 123456 | region2 | 963258 | area 2 2
| 21345
The advantage of this structure is that if the organisation structure
changes then the data changes, not the structure of the database.

Terry
"Max" <ma******@msn.com> wrote in message
news:4f**************************@posting.google.c om...
One of the most common situations I keep finding myself in when
creating or working on an MS Access db is figuring out how to relate a
table that actually has a place in more than 1 tiered one-to-many
related tables. Let me explain: The easiest example is a phone
number in a multi-level organized company...

Lets say you have a main Regional Office. Under that Regional Office,
you have about 5 or 6 Area Offices that are all considered part of
that Regional Office. Under each Area Office, there are about 12-15
Branch Offices that are all considered Part of that Area Office. And
under each Branch Office, there would be Employees all considered to
be under that Branch. This would make tables with the following
relationship to each other:

[Regional Office]-OneToMany-[Area Office]-OneToMany-[Branch
Office]--OneToMany--[Employees]

On the lowest level [Employees] you could make a [Phone#] field. You
would think this would be enough, since a phone number should be
associated at an employee level. But what if (as the case actually
is) there is a phone number specifically for a Branch as a whole; a
main phone number for the branch. And, there is a specific phone
number for each Area Office...and a specific phone number for the
Regional office.

Obviously, it would be a good idea to make a separate [Phones] table.
But here then is the question: How do you relate it to the tiered
tables? What is the most "normalized" way to do that?

MS Access books never seem to address this particular scenario, even
though I see it come up situationally all the time.



Nov 12 '05 #6

P: n/a
Max
"Terry Kreft" <te*********@mps.co.uk> wrote in message news:<bl**********@newsreaderg1.core.theplanet.net >...

Terry, your entire idea is a very good one... and I will definately
consider it. Only I don't know SQL language, I use the Query design
veiw, but I'm sure I can figure what you're saying there.

I have a lot to consider in restructuring it, because I've already
built an almost completed report based on the current structure. But
I guess this is the very situation I'd be in if the organization
changed... so it's worth making the major adjustment...

I'll look into this further. Thanks for your input!
Nov 12 '05 #7

P: n/a
Max
"PC Datasheet" <av**********@nospam.com> wrote in message news:<WG*******************@newsread1.news.atl.ear thlink.net>...

I see your point here. You're right... however I was purposely
"cheating" in a sense because the actual scenario that I used the
example of the Phones for (to keep the concept simple) is really the
Cost Center Number. I wanted to have a way to attach the Cost Center
to each level of the organization...and in the Employees table I
wanted to have 1 field called CostCenter that was a lookup field,
which included the Office level in the lookup field. Like - "7999
West Valley Branch", which meant the Employee was part of the 7999
Cost Center. And the Regional Manager would have "8899 Region 88
Office" (Cost Centers & areas are fictitious for Information Security
purposes).

I will though, take a look at it again and see if I can achieve this
with the proper structure you suggest in your reply. Thanks for your
input.
Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.