Complex Table Design

[mabedin]

Hi,

DESCRIPTION
------------------------
I am trying to build a simple CRM solution on Ms Access.

I have a table where I have basic sales leads information such as customer's
name, address, and telephone number (tblContacts). I have made a relationship
with agents table (tblAgents).

tblContacts contains following fields:
ContactID (AutoNumber) AgentID (Number), ContactName (Text), ContactAddress
(Text), ContactTelephone (Text), LeadStatus (Yes/No)

tblAgents contains following fields:
AgentID (AutoNumber), AgentName (Text), AgentAddress (Text), AgentTelephone
(Text)

Supervisor has the authority to change the LeadStatus either make it active
or non-active for calling purposes. Furthermore, only supervisor can assign
the same customer to different agent. Only one agent can be assigned to one
customer.

INFORMATION
-----------------------
tblContacts is getting agents information from tblAgents.


NEED/WANT
--------------------
I need/want to do two things in the design.

1. I need/want to have a lead status (field_name: LeadStatus - YES/NO) If YES,
then lead is active to call. If NO, then lead is not active to call. ******I
have designed and tested this part.*******.

2. I need/want many agents to call one customer so that I can assign the same
lead to more than one agent. ********I can't figure it out how ot design
this part*******.

Please help. I would really appreciate.

Thank you

 

[Damian S]

Hi mabedin,

If you need to link more than one Agent to a Contact, you will need to
simulate a many to many join by using a linking table like this:

tblContactAgentLead
ContactAgentLeadID - Autonumber
ContactID - Number - Maps to ContactID in tblContact
AgentID - Number - Maps to AgentID in tblAgent
Other Lead related tables as necessary.

This is how you would connect one Contact to many Agents for leads, but the
Contact is still assigned to a single Agent through the link in the
tblContact.

If you wanted multiple Agents assigned to each Contact, you could use a
similar design, but remove the AgentID in tblCustomer.

Hope that helps.

Damian.

 

[mabedin via AccessMonster.com]

Thanks Jamie, this NIH make sense.

See:

http://en.wikipedia.org/wiki/Not_Invented_Here

Jamie.

--