Company Contacts

[Mcleaver via AccessMonster.com]

I am making a database for a call center so they can look up people,
companies and the duties that these contacts are responsible for so they can
refer problems to the right people or give out correct adresses, numbers etc
to our customers. Right now all the information is very unaccessable and you
are on your own to gather this info.
So far I have come up with that I have a main table - Company and the primary
key for this is the company name. My reasoning is that I never want to veiw a
report in the future with the possbility that it might show the value instead
of the name and - this is a unique field. There will be no duplicates.
My subforms are:
Department and it's subform is
Group/Team/ Division.

My problem is I need to add people to these groups. Not every company will
have a department or group the people will simply be under co.
The majority of my contacts will. This table is mostly for contacting people
within our company and we have many divisions.
I could have a subform for each breakdown- ie- company with a subform, dept
with a subform etc.
But when I want to be able to search for a person and view there info I would
have to do a search under each group and that is too time consuming for a
person who has to find info quickly. Also I need to use an expression base on
all the people to fill in the - 'Who is in charge of this group field'

Also- Each person has duties and (this is the most useful part of this
database). Each person in our co. has things others need to refer to them and
a certain way to refer to them. We get emails daily as to how to refer a
customer issue to a certain person. This database is a way to update this
info and add the person resposible for this. The call center needs to enter
a key word and be able to see be able to see who is responsible for this and
the field that has instructions for how to refer this to them.


Sorry this is long but I have tried at least three mock ups of this type of
database and have encounter a major problem with some aspect of it. Thanks
in advance!

 

[Allen Browne]

Sounds like you need a great deal of flexibility to handle the relationship
between the entities, such as:
companies that contain divisions
companies that contain people
divisions that contain people
divisions that contain grouping of people
sections of divisions that contain groupings of people, with different
roles.

A structure that is capable of handling that diversity is described here:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html
Download the example and see if you can adapt the data structure to your
needs.

 

[Mcleaver via AccessMonster.com]

Thanks. I have been looking for a similar sample and this is very close. Will
examine.
Small question for a thought I had.
Could I have an 'Individuals' table that was a one to many relationship- link
it (add the primary keys for) the company table, the dept table, and the
group table so that I could subform these to each table while the dept table
was subformed under the co. table, and the group table was under the dept.
table?

Sounds like you need a great deal of flexibility to handle the relationship
between the entities, such as:
companies that contain divisions
companies that contain people
divisions that contain people
divisions that contain grouping of people
sections of divisions that contain groupings of people, with different
roles.

A structure that is capable of handling that diversity is described here:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html
Download the example and see if you can adapt the data structure to your
needs.

I am making a database for a call center so they can look up people,
companies and the duties that these contacts are responsible for so they

[quoted text clipped - 46 lines]

Thanks
in advance!

 

[Allen Browne]

If I understand you, you are suggesting these tables and one-to-many
relationships:
Company -> Individual
Company -> Dept
Dept -> Group

That may be okay for your needs, but it would not permit things like:
- an individual who works for multiple companies,
- an individual who is associated with a department,
and so on.

Additionally, if you have a table where you do some kind of business with
all these entities, which one do you relate it to? For example, should the
foreign key of your Invoice table be the Company table? the Individual
table? Might you need to invoice a Dept? or a Group?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks. I have been looking for a similar sample and this is very close.
Will
examine.
Small question for a thought I had.
Could I have an 'Individuals' table that was a one to many relationship-
link
it (add the primary keys for) the company table, the dept table, and the
group table so that I could subform these to each table while the dept
table
was subformed under the co. table, and the group table was under the dept.
table?

Sounds like you need a great deal of flexibility to handle the
relationship
between the entities, such as:
companies that contain divisions
companies that contain people
divisions that contain people
divisions that contain grouping of people
sections of divisions that contain groupings of people, with different
roles.

A structure that is capable of handling that diversity is described here:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html
Download the example and see if you can adapt the data structure to your
needs.

I am making a database for a call center so they can look up people,
companies and the duties that these contacts are responsible for so they

[quoted text clipped - 46 lines]

Thanks
in advance!

 

[Mcleaver via AccessMonster.com]

I agree-

My newest relationship was -
Company
subtables Dept & Contacts (with subtable of referall items per person)
Subtables to Dept:
Group &Contacts (with subtable of
referall items per person)
Subtable for Group would be its Contacts
(with subtable of referall items per person)

This would give me everyone and all the groups proper order and when I
printed out a directory or made a web page I could list people correctly. But,
If I made searches it just wouldn't work.

If I understand you, you are suggesting these tables and one-to-many
relationships:
Company -> Individual
Company -> Dept
Dept -> Group

That may be okay for your needs, but it would not permit things like:
- an individual who works for multiple companies,
- an individual who is associated with a department,
and so on.

Additionally, if you have a table where you do some kind of business with
all these entities, which one do you relate it to? For example, should the
foreign key of your Invoice table be the Company table? the Individual
table? Might you need to invoice a Dept? or a Group?

Thanks. I have been looking for a similar sample and this is very close.
Will

[quoted text clipped - 30 lines]