parsing data from queries, is this possible?

[patrichek]

Hi,
I am trying to devise a way to take a spreadsheet that we receive daily, containing the day's leads, which is then sorted and distributed according to certain column's variables.
I have no problem linking the spreadsheet to access, but I don't know which query functions to try in order to parse the data into groups so I can have the leads distributed equally to our salespeople.

We have 10 salespeople receiving leads and usually about 150 leads per day.

an example of this spreadsheet would be:

Name, state, bestcontacttime

J.Smith, PA, 8-10am
D.Glenn, GA, 10-12pm
H.Scott, LA, 12-2pm

what we do is sort the leads by bestcontacttime and then divide that by 10(number of salespeople) and give each sales rep an equal amount. We try to give each salesperson an equal amount of 8-10, 10-12 and 12-2pm leads.

Is there a way i can do this using access?

thanks!

 

[Crowley16]

you could try to first group the data into the time periods, and then count up the entries in each group and use integer division by 10 to give you a number...

something like:

select name, time, count(time)%10
from tblName
group by name, time

the % is integer division or modulo

or something like that...

--------------------
Procrastinate Now!

 

[patrichek]

So I should make a separate query for each time period and then use group by to assign them to the salesperson, right?

What will happen if there is an odd number of leads?

I normally use query designer because i don't know sql very well, is it possible to build this on the query grid?

Thanks!

 

[Crowley16]

on second though, you can do it easier than that, as long as you have a counter for each row, you can just integer divide the counter by 10 to give a number between 1 and 10, corresponding to the 10 representatives...


--------------------
Procrastinate Now!

 

[patrichek]

I'm not sure I'm following you when you say "a counter for each row" ?
can you give me an example?

 

[Crowley16]

eh, how's about:

1 john ST 8-12
2 dave AK 12-14
...

--------------------
Procrastinate Now!