I'm more than one french fry short of a happy meal: dang this problem!

I have a customer table that lists all of our customers, and a contacts table that lists all of our contacts. Now it has been decided that rather than just knowing the *title* of our contacts, we should also associate each contact into one of 30 predefined "roles". (this because titles in our customer base vary widely, and we need to know regardless of what the title is, who can perform certain functions-- like authorizing our invoices, etc.)

Example:
Customer 1, Contact 1, Title, Role = Pain In the Butt, Inc., Mr. Self Assured, President, Buyer Level 1
Customer 1, Contact 2, Title, Role = Pain in the Butt, Inc., Ms. KnowitAll, IT Manager, Primary IT Call Point
Customer 1, Contact 2, Title, Role = Pain in the Butt, Inc., Mr. Nerdley, Engineering Manager, Secondary Eng. Call Point

I could list these roles as an additional field in the contacts table... but... what I have been told is needed is this:

Each time a new customer is added to the database, automatically a list of the thirty roles will populate into some area of the database. That way when the sales guys want to know who "chief cook and bottle washer" is, they can query by that role- regardless of titles, and also it'll be glaringly obvious that the fields *aren't* populated if the responsible person hasn't done their data entry.

I'm clueless on how to "force" these new roles to populate a new table, or even to populate (without adding other info) into the contacts table. It would be really awful to add these roles as additional fields in the Customer table itself.

I'm so confused.... argh... does anyone have a valium?? Please help me!

Gina

Gina,

I am not sure of the meaning of "a list of the thirty roles will
populate into some area of the database". But here's how I would
approach it, in terms of table design...

Table: Customers
CustomerID
CustomerName
Address
other company-specific fields

Table: Contacts
ContactID
CustomerID
Title
Phone
Other contact-specific fields

Table: Roles
RoleID
NameOfRole

Table: Designations
DesignationID
ContactID
RoleID

So, this assumes any given person can be designated in more than one
role. Therefore, if Pain In the Butt Inc. is CustomerID = 1, and Ms.
KnowitAll is ContactID = 33, and the Roles table lists the 30
pre-determined categories you want to use, in which 'Primary IT Call
Point' is listed as RoleID = 4 and 'Chief cook and bottle washer' is
listed as RoleID = 7, both of which describe Ms KnowitAll's position,
then the Designations table will show records like...
1 33 4
2 33 7

As for the data entry processes, you would probably use a combobox with
its row source set to the Roles table to enter the RoleID into a subform
based on the Designations table.

--
Steve Schapel, Microsoft Access MVP


Gina Starr wrote:
I have a customer table that lists all of our customers, and a
contacts table that lists all of our contacts. Now it has been
decided that rather than just knowing the *title* of our contacts, we
should also associate each contact into one of 30 predefined "roles".
(this because titles in our customer base vary widely, and we need to
know regardless of what the title is, who can perform certain
functions-- like authorizing our invoices, etc.)

Example: Customer 1, Contact 1, Title, Role = Pain In the Butt,
Inc., Mr. Self Assured, President, Buyer Level 1 Customer 1, Contact
2, Title, Role = Pain in the Butt, Inc., Ms. KnowitAll, IT Manager,
Primary IT Call Point Customer 1, Contact 2, Title, Role = Pain in
the Butt, Inc., Mr. Nerdley, Engineering Manager, Secondary Eng. Call
Point

I could list these roles as an additional field in the contacts
table... but... what I have been told is needed is this:

Each time a new customer is added to the database, automatically a
list of the thirty roles will populate into some area of the
database. That way when the sales guys want to know who "chief cook
and bottle washer" is, they can query by that role- regardless of
titles, and also it'll be glaringly obvious that the fields *aren't*
populated if the responsible person hasn't done their data entry.

I'm clueless on how to "force" these new roles to populate a new
table, or even to populate (without adding other info) into the
contacts table. It would be really awful to add these roles as
additional fields in the Customer table itself.

I'm so confused.... argh... does anyone have a valium?? Please help
me!

Gina

lol....love your list of roles! if you can get your boss to go for them,
i'll trade you my boss for yours! g


"Gina Starr" wrote in message
...
I have a customer table that lists all of our customers, and a contacts
table that lists all of our contacts. Now it has been decided that rather
than just knowing the *title* of our contacts, we should also associate each
contact into one of 30 predefined "roles". (this because titles in our
customer base vary widely, and we need to know regardless of what the title
is, who can perform certain functions-- like authorizing our invoices, etc.)

Example:
Customer 1, Contact 1, Title, Role = Pain In the Butt, Inc., Mr. Self
Assured, President, Buyer Level 1
Customer 1, Contact 2, Title, Role = Pain in the Butt, Inc., Ms.
KnowitAll, IT Manager, Primary IT Call Point
Customer 1, Contact 2, Title, Role = Pain in the Butt, Inc., Mr. Nerdley,
Engineering Manager, Secondary Eng. Call Point

I could list these roles as an additional field in the contacts table...
but... what I have been told is needed is this:

Each time a new customer is added to the database, automatically a list of
the thirty roles will populate into some area of the database. That way
when the sales guys want to know who "chief cook and bottle washer" is, they
can query by that role- regardless of titles, and also it'll be glaringly
obvious that the fields *aren't* populated if the responsible person hasn't
done their data entry.

I'm clueless on how to "force" these new roles to populate a new table, or
even to populate (without adding other info) into the contacts table. It
would be really awful to add these roles as additional fields in the
Customer table itself.

I'm so confused.... argh... does anyone have a valium?? Please help me!

Gina

LOL...I agree.

On Sat, 22 May 2004 17:26:02 -0700, "Gina Starr"
wrote:

I have a customer table that lists all of our customers, and a contacts table that lists all of our contacts. Now it has been decided that rather than just knowing the *title* of our contacts, we should also associate each contact into one of 30 predefined "roles". (this because titles in our customer base vary widely, and we need to know regardless of what the title is, who can perform certain functions-- like authorizing our invoices, etc.)

Gina, did you ever get a resolution to this problem?


John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public