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.
7  1834 
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.c om> wrote in message
news:4f******** *************** ***@posting.goo gle.com... 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.
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
RegionalOfficeI D 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 AreaOfficeTeleN um as a field in the Area Office table to record that
attribute. Your Area Office table then looks like:
TblAreaOffice
AreaOfficeID
RegionalOfficeI D
AreaOfficeTeleN um
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.c om> wrote in message
news:4f******** *************** ***@posting.goo gle.com... 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.
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.c om> wrote in message
news:4f******** *************** ***@posting.goo gle.com... 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.
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*********@mp s.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.c om> wrote in message
news:4f******** *************** ***@posting.goo gle.com... 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.
That piece of software was originally written by Dev.
Terry
"HSalim" <On************ ******@msn.com> wrote in message
news:FQ******** *********@nwrdn y03.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*********@mp s.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.c om> wrote in message
news:4f******** *************** ***@posting.goo gle.com... 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.
"Terry Kreft" <te*********@mp s.co.uk> wrote in message news:<bl******* ***@newsreaderg 1.core.theplane t.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!
"PC Datasheet" <av**********@n ospam.com> wrote in message news:<WG******* ************@ne wsread1.news.at l.earthlink.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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Myster Ious |
last post by:
Polymorphism replaces switch statements, making the code more
compact/readable/maintainable/OO whatever, fine!
What I understand, that needs to be done at the programming level, is
this:
a switch-case has a variable (most probably an enumeration) &
associated symbols or integral value. Selection is made, base on what
symbol/value the variable holds. So
|
by: lime |
last post by:
I have addressed most of the issues listed in the responses to my last post
"Critique CSS layout (1st go - fingers crossed)". I'm rapt with the progress
I have made, thanks to all for your past critiques.
http://www.limelightstudio.com.au/iss/8/
The issues addressed:
- Nav uses <li> now
- No horizontal scroll in IE6 at 850-900 pixels
|
by: noid droid |
last post by:
Hi. I posted yesterday asking if C# lived up to the hype. Thus far the
feedback has been all positive. (Thanks.)
Can anyone suggest GOOD books for learning C# and the Visual Studio .NET
IDE? If there is a book that covers both, that would be great. Also, is
it necessary for me to learn a lot about the .NET framework that wouldn't
also be covered in one of the books? If I can find a book that'll get me
up and running with the IDE...
|
by: Earl |
last post by:
I've been on a mission this past year to move from VB6 to .Net. I'm
fortunate(!?) not to have the big catalog of some long-time VB developers,
although I have some CE apps that have to be entirely rewritten in CF. A lot
of what I develop has to be written for the PPc, so I knew I would be
looking for information in .Net, ADO.Net, CF -- and SQL Server.
So in the spirit that others might benefit from my journey, here are my
observations....
|
by: Carter Smith |
last post by:
http://www.icarusindie.com/Literature/ebooks/
Rather than advocating wasting money on expensive books for beginners,
here's my collection
of ebooks that have been made freely available on-line by their authors.
There are lots of them out there but this selection cuts out the junk.
If you know of any other good books that are freely available please post a
link to them here and I'll consider adding them to the site.
| | |
by: John |
last post by:
I am looking for VB.Net books which provide a lot of exercises (preferrably
with all the answers worked out on CD).
In earlier reactions Ken Tucker and M.Posseth recommended a few books.
Thanks for both of your reactions.
Ken included the links, so it is clear which books he recommended (I will
get them!)
M.Posseth recommended "the complete MCSD self paced training kit" and
"Programming Microsoft Visual Basic form Francesco Balena",...
|
by: youpak2000 |
last post by:
What you can't find in programing text books
Professional software development needs more knowledge than language
syntax,
OOP, styles, etc. There are many things which people usually learn by
working in
industry. One important part of the development is good knowledge of
tools
such as compilers, linkers, and make file design. There are no in-dept
discussion of these tools in C/C++ programming text books. I haven't
|
by: David |
last post by:
i think i just realized i'm an idiot. again.
(not syntactically correct code... just pieces to illustrate)
class StateObject
{
members like socket, receiveBuffer, receiveBufferSize, StringBuilder
etc..
}
|
by: Hypnotik |
last post by:
Hello everyone. I'm working on a program which uses a class (Shelf) and a struct (Book). In the main program we declare an array (Library) which is of type shelf. The program takes in various info about books (author, title, isbn, etc) and then allows you to search for books starting with a certain letter, and in turn displays those books and the info on them.
At this point I can enter the info, and display the info for 1 book. I'm confused...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| | |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
