Is this possible to do with Access 2000???

[WB786]

I have been told to design a database where we get sales leads with how much a person is going to be spending. We want the person with high dollar amount to go to more experienced employee but then only assign certain amount of leads to them, say just 3 for today and then next 2 to someone else randomly, for example. We get the leads in Excel format. I know how to import them in with VBA to automate the whole process. But how to assign them to different level of employees? I was told to have this done by Friday, but if this is not a possible option in Access then I can let my manager know. Anyone please let me know if you have any idea on this one.

THANKS IN ADAVANCE.

-WB

 

[FancyPrairie]

I'm not sure I totally understand what you're trying to do, but I'll give it a shot.

First, you need to setup a table that defines the information you need to complete the task.

You will need to know the follow things:
1. Employee names (or id)
2. Levels to which they have been assigned (whether it's a number (i.e. 1-5) or a $ amount range
3. Date field to indicate when the job was assigned
4. Defined the Jobs by ID or name.

To start, I would create the following tables:

tblEmployee
lngEmpID AutoNumber (represents Primary Key)
strLastName Text (32)
strMI Text (1)
strFirstName Text (32)
strFullName Text (68) (i.e. Last, First M.)
intLevel Number (integer) (represents level)
Whatever else you need to know about the employee

tblJob
lngJobID AutoNumber (represents Primary Key)
strDesc Text (255) (Name of the job)
lngEmpID Number (long) Employee assigned to the job
dteAssigned Date/Time Date employee assigned to job

Now you might have a form that defines each job. On the form, you may have a combobox listing the names of all employees that can be assigned to the job. However, the combobox must be queried to filter out employees that don't meet the level requirements or have already been assigned x number jobs for the given date.

About having it done by Friday. Depending on your skill level, you may have something ready. However, I doubt that it can be done by Friday and have it idiot proof. You will need forms so the users can add records to your lookup tables without calling you to do it. And, how many lookup tables will you need? In my example above, you have one main table (tblJob) and one lookup table (tblEmployee). What other information will you need to gather.

 

[jalby]

WB,

Have you set up all your tables yet? It sounds like you could use an action query to append the results to the HighDollarEmployee and LowDollarEmployee tables and then set up your reports off of the queries as well as utilizing a send.Object to mail the results to the Employee Supervisor.

Possibly in the parameters of the query you could tell it based on the workload of the employees in the table who to send the account to.

If contact.spenditure => (amount)
If contact.spenditure =< (amount)

I am a rookie to access so I apologize ahead of time if this approach does not give you the desired outcome.

 

[WB786]

I didn't want to post too much info at first. But to be more detailed. I already have built the employee database and is running great on my sql server.

I know I can have a working demo model by Friday with some help. I know enough VBA but when it comes to automatically assign records to employees is where I need the coding for.

The process is going to be that every morning I will dump the leads from Excel into an temp Access table, clean it up if needed and then append it to the leads database on the sql server. This new leads database will then assign leads to employee depending on level of expertise.

The employee logs in and based on userid will only see their assigned records and any previously assigned records. This part I already know how to do.

I guess what I am asking is the coding that I will need to assign a lead to an employee. Step by Step if all possible. The leads will have Customer LName, FName, Address, Phone, Amount, etc.

Please let me know if this help or if you need more info. Thanks.

-WB

 

[WB786]

I have not gotten a response back yet. But is it possible to setup a query or something that will assign leads automatically to employees based on their skill level. Please HELP anyone. Thanks in advance.


-WB