Designing a small CRM system

I need to design a simple crm system for two sales persons we have. I
don't want to spend too much time putting this together or maintaining
it because this is a side project that I've had to squeeze into my task
list. It seems simple, but I may be wrong... :)

We just hired two telesales persons, Tanya and Charles. They will be
doing mass calling off of lists pulled from our database on several
products and services. I need to set up some sort of database that
will allow me to upload and append different lists of people (and there
will be some overlap between these lists) and then tie these lists to
an activity table that will allow them to log in their calls. I set up
a form with a subform and allows them to create activity for each
contact, so I have that done. What I'm having trouble with is how to
design this database so that I can import a new list into access and
append it to the table and then know where that list has come from
(call list for topic A, B, C, etc). Some people will fall on multiple
lists (topic A and B). How do design these tables to do this? I also
want to make sure that Tanya and Charles don't call on the same people.
Any advice appreciated. I've never designed a database before, so be
easy on me. :) Thanks.

re: Designing a small CRM system

It sounds like you have a Many-To-Many relationship between People and
Topics. Maybe play with the following table structure:

Table: People
ID - Autonumber (primary key)
<<Datafields specific to a person>>

Table: Topics
ID - Autonumber (primary key)
<<Datafields specific to a topic>>

Table: Activity
ID - Autonumber (primary key)
fkPeopleID (foreign key - indexed, dups OK) [link to People table]
fkTopicID (foreign key - indexed, dups OK) [link to Topics table]
<<Datafields specific to activity - call time, issue, etc.>>

This structure yields a many-to-many relationship between People and
Topics. Of course, you'll have to play with this structure to get it
to meet your requirements, but I think this is the basic idea you're
hitting on.

When you append new data, you will have to enter something in the
Activity table to associate a Person with a topic if you already know
this association. Maybe a status field that links to another table
(like a look-up), that shows 1 for entered, 2 for Assigned to Tanya, 3
for Assigned to Charles, etc. You could use this to ensure that noone
gets called twice on the same topic.

I'm sure this is clear as mud. Please post-back and I'll try to answer
your questions.

HTH,
Johnny

re: Designing a small CRM system

Yeah, I think that makes sense and I've started putting it together a
little bit. So does this structure allow me to make a form for the
People table with a subform of the Activity table and then be able to
enter in activity for either topic?

re: Designing a small CRM system

Yes it will, but it won't be as straight-forward as a simple
one-to-many. I would envision that the main form would be bound to the
People table, and the subform would be a query that joins the topics
table to the activity table.

Again, this will have to be adjusted to ensure that you can do updates
in the subform.

Also, don't rule out the possibility of using two forms to do this, one
to select a person, and one to do the activity.

re: Designing a small CRM system

I'm running into another problem. I don't know an easy way to add
lists to this. For instance, I have a list of customers that I want
them to call for Product A and then I have a list of customers that
they need to call for Product B (and there will be overlap between the
two). I don't know if this structure enables me to do this. The
actual call activity will go in the Activities table, but how to I
assign products to customers before they call them? Am I making sense?

re: Designing a small CRM system

See next to last paragraph of my first post. I think you would have a
status field in the Activity table to represent this association.
Here's an example:

You have the following list to import:

People Topic
A XX
B YY
B ZZ

First, convert the people column and the topic column to primary keys
(I'm skipping the step where you import people and topics separately,
ensuring no duplication):

People Topic
1 1
2 2
2 3

Then, add status (a status of 1 means ready to call, but not called
yet):

People Topic Status
1 1 1
2 2 1
2 3 1

Finally, import the data to the Activity table. I would write code in
DAO that automates this entire process (I import from excel, text
files, etc. all the time, unfortunately!) I can help you with that if
necessary. Please let me know if the design I'm describing makes
sense.

Johnny