If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
I'm more than one french fry short of a happy meal: dang thisproblem!
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 |
#3
|
|||
|
|||
I'm more than one french fry short of a happy meal: dang this problem!
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 |
#4
|
|||
|
|||
I'm more than one french fry short of a happy meal: dang this problem!
LOL...I agree.
|
#5
|
|||
|
|||
I'm more than one french fry short of a happy meal: dang this problem!
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 |
Thread Tools | |
Display Modes | |
|
|