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

[Gina Starr]

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
Customer 1, Contact 2, Title, Role = Pain in the Butt, Inc., Ms. KnowitAll, IT Manager, Primary IT Call Poin
Customer 1, Contact 2, Title, Role = Pain in the Butt, Inc., Mr. Nerdley, Engineering Manager, Secondary Eng. Call Poin

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

 

[Steve Schapel]

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.

 

[tina]

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>

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.

 

[John Vinson]

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?