Assign leads by percentage

[jbiggs via AccessMonster.com]

I am working on a query to assign leads by percentage available. So for
example if we have:

25 Source A
10 Source B
15 Source C

And I want to assign 10 leads by a user, it would take 5 from Source A, 2
from Source B and 3 from Source C. We have many lead sources and I want to
take up the least amount of resources when doing this. Should I use several
counts, a SQL query??

 

[Michel Walsh]

SELECT -int(- [Total Wanted] * Lead / (SELECT SUM(Lead) FROM tableName) ),
source
FROM tableName



where [Total Wanted] is a parameter, kind of the 10 in your example, and
where Lead and Source are two fields in your table.



It may happen you will get MORE than the total wanted. As example, if you
ask for 9, instead of 10, you will get

-int(-9*25/50) = 5 for source A
-int(-9*10/50) = 2 for source B
-int(-9*15/50) = 3 for source C

for a total of 10. In fact, which source should be one less than what we
computed?

If there is no need to have integer solution (because it is a weight, or a
volume, or something similar),

SELECT [Total Wanted] * Lead / (SELECT SUM(Lead) FROM tableName) , source
FROM tableName



Hoping it may help,
Vanderghast, Access MVP

 

[jbiggs via AccessMonster.com]

I can see where I was unclear in the first post. I need to find a way to
calculate [Total Wanted] from your query.

I need to do count all the lead sources and store all those values somewhere
along with a total. I then need to assign leads based on these numbers. I
am unsure on the best method to do this.

SELECT -int(- [Total Wanted] * Lead / (SELECT SUM(Lead) FROM tableName) ),
source
FROM tableName

where [Total Wanted] is a parameter, kind of the 10 in your example, and
where Lead and Source are two fields in your table.

It may happen you will get MORE than the total wanted. As example, if you
ask for 9, instead of 10, you will get

-int(-9*25/50) = 5 for source A
-int(-9*10/50) = 2 for source B
-int(-9*15/50) = 3 for source C

for a total of 10. In fact, which source should be one less than what we
computed?

If there is no need to have integer solution (because it is a weight, or a
volume, or something similar),

SELECT [Total Wanted] * Lead / (SELECT SUM(Lead) FROM tableName) , source
FROM tableName

Hoping it may help,
Vanderghast, Access MVP

I am working on a query to assign leads by percentage available. So for
example if we have:

[quoted text clipped - 9 lines]

several
counts, a SQL query??

 

[Michel Walsh]

I am lost. Anyhow, if you just need another SUM, that is a matter to write a
sub query:


SELECT ( SELECT SUM(whaterver) FROM somewhere) * Lead / (SELECT
SUM(lead) FROM tableName) , source
FROM tableName


You note that the parameter [Total Wanted] has been replaced with

( SELECT SUM(whatever) FROM somewhere )


where whatever is the field for which you have to sum the values, from a
table, another table, I assume, I called it "somewhere", here.


Vanderghast, Access MVP

I can see where I was unclear in the first post. I need to find a way to
calculate [Total Wanted] from your query.

I need to do count all the lead sources and store all those values
somewhere
along with a total. I then need to assign leads based on these numbers.
I
am unsure on the best method to do this.

SELECT -int(- [Total Wanted] * Lead / (SELECT SUM(Lead) FROM tableName) ),
source
FROM tableName

where [Total Wanted] is a parameter, kind of the 10 in your example, and
where Lead and Source are two fields in your table.

It may happen you will get MORE than the total wanted. As example, if you
ask for 9, instead of 10, you will get

-int(-9*25/50) = 5 for source A
-int(-9*10/50) = 2 for source B
-int(-9*15/50) = 3 for source C

for a total of 10. In fact, which source should be one less than what we
computed?

If there is no need to have integer solution (because it is a weight, or a
volume, or something similar),

SELECT [Total Wanted] * Lead / (SELECT SUM(Lead) FROM tableName) , source
FROM tableName

Hoping it may help,
Vanderghast, Access MVP

I am working on a query to assign leads by percentage available. So for
example if we have:

[quoted text clipped - 9 lines]

several
counts, a SQL query??

 

[jbiggs via AccessMonster.com]

Here is what I have so far:

A query that will tell me how many leads I have available for each Source.

Now, I need to figure out how to store each of these numbers in a variable so
I can do calculations and assign the appropriate number or each.

I am lost. Anyhow, if you just need another SUM, that is a matter to write a
sub query:

SELECT ( SELECT SUM(whaterver) FROM somewhere) * Lead / (SELECT
SUM(lead) FROM tableName) , source
FROM tableName

You note that the parameter [Total Wanted] has been replaced with

( SELECT SUM(whatever) FROM somewhere )

where whatever is the field for which you have to sum the values, from a
table, another table, I assume, I called it "somewhere", here.

Vanderghast, Access MVP

I can see where I was unclear in the first post. I need to find a way to
calculate [Total Wanted] from your query.

[quoted text clipped - 36 lines]

 

[Michel Walsh]

In VBA? Open a recordset and, if required, get the data using the method
GetRows of the recordset.


Vanderghast, Access MVP

Here is what I have so far:

A query that will tell me how many leads I have available for each Source.

Now, I need to figure out how to store each of these numbers in a variable
so
I can do calculations and assign the appropriate number or each.

I am lost. Anyhow, if you just need another SUM, that is a matter to write
a
sub query:

SELECT ( SELECT SUM(whaterver) FROM somewhere) * Lead / (SELECT
SUM(lead) FROM tableName) , source
FROM tableName

You note that the parameter [Total Wanted] has been replaced with

( SELECT SUM(whatever) FROM somewhere )

where whatever is the field for which you have to sum the values, from a
table, another table, I assume, I called it "somewhere", here.

Vanderghast, Access MVP

I can see where I was unclear in the first post. I need to find a way to
calculate [Total Wanted] from your query.

[quoted text clipped - 36 lines]

several
counts, a SQL query??

 

[jbiggs via AccessMonster.com]

Here is the code I have so far:

Set RS = db.OpenRecordset(qryUnassignedNewLeads)
Do Until RS.EOF
Percent = CountofSource / SumLeads
NumLeads = Percent * CountofSource
DoCmd.RunSQL "UPDATE clients SET salesperson = " & Text15 & " WHERE ID IN
( SELECT TOP " & NumLeads & " ID FROM clients WHERE Salesperson IS NULL And
[task note] = 'Unworked Lead' And Source = """ & Source & """ )"

RS.MoveNext
Loop

The qryUnassignedNewLeads returns 2 items, Source and CountofSource. How can
I calculate SumLeads, which is the Sum of all CountofSource.

Also, does the syntax look OK?

In VBA? Open a recordset and, if required, get the data using the method
GetRows of the recordset.

Vanderghast, Access MVP

Here is what I have so far:

[quoted text clipped - 26 lines]

 

[Michel Walsh]

DSum("CountOfSource", "qryUnassignedNewLeads") should sum all the values
under countOfSource for the said saved query.


As for the syntax goes, seems you need rs.Fields("CountOfSource") rather
than just CountOfSource, in the two lines following Do Until rs.EOF


As for the update, it seems you are updating MANY records, not just one, and
you don't specify an ORDER BY, which is highly un-usual, when using a
SELECT TOP.


Hoping it may help,
Vanderghast, Access MVP

Here is the code I have so far:

Set RS = db.OpenRecordset(qryUnassignedNewLeads)
Do Until RS.EOF
Percent = CountofSource / SumLeads
NumLeads = Percent * CountofSource
DoCmd.RunSQL "UPDATE clients SET salesperson = " & Text15 & " WHERE ID
IN
( SELECT TOP " & NumLeads & " ID FROM clients WHERE Salesperson IS NULL
And
[task note] = 'Unworked Lead' And Source = """ & Source & """ )"

RS.MoveNext
Loop

The qryUnassignedNewLeads returns 2 items, Source and CountofSource. How
can
I calculate SumLeads, which is the Sum of all CountofSource.

Also, does the syntax look OK?

In VBA? Open a recordset and, if required, get the data using the method
GetRows of the recordset.

Vanderghast, Access MVP

Here is what I have so far:

[quoted text clipped - 26 lines]

several
counts, a SQL query??

 

[jbiggs via AccessMonster.com]

Current code:

Set db = CurrentDb()
Set rs = db.OpenRecordset("qryUnassignedNewLeads", dbOpenDynaset)
SumLeads = DSum("CountOfSource", "qryUnassignedNewLeads")
Do Until rs.EOF

Percent = rs.Fields("CountOfSource") / SumLeads
NumLeads = CInt(Percent * rs.Fields("CountOfSource"))
CurrentSource = rs.Fields("Source")
DoCmd.RunSQL "UPDATE clients SET salesperson = " & Text15 & " WHERE ID IN
( SELECT TOP " & NumLeads & " ID FROM clients WHERE Salesperson IS NULL And
[task note] = 'Unworked Lead' And Source = " & CurrentSource & " )"

rs.MoveNext
Loop

Percent seems to be calculating correctly but NumLeads keeps getting set to
zero. I am also having a problem with setting CurrentSource, that rs command
doesn't seem to be working.

DSum("CountOfSource", "qryUnassignedNewLeads") should sum all the values
under countOfSource for the said saved query.

As for the syntax goes, seems you need rs.Fields("CountOfSource") rather
than just CountOfSource, in the two lines following Do Until rs.EOF

As for the update, it seems you are updating MANY records, not just one, and
you don't specify an ORDER BY, which is highly un-usual, when using a
SELECT TOP.

Hoping it may help,
Vanderghast, Access MVP

Here is the code I have so far:

[quoted text clipped - 27 lines]

 

[Michel Walsh]

How have you declared the variable Percent? If you declared it as integer,
then, indeed, it will truncate the percentage (a value between 0 and 1) to
0. If Percent is zero, then NumLeads will also.

I don't understand what you mean by "CurrentSource", given the context. Is
it rs.Fields("Source") ? Is it a number or an alphanumeric string? If it
is alpha, you will need to add the appropriate delimiter, in the text of the
SQL command you are building.


I still find your "UPDATE ... IN (SELECT TOP ... )" quite out of the
ordinary (updating multiple records, and no ORDER BY in the sub-query).


Vanderghast, Access MVP

Current code:

Set db = CurrentDb()
Set rs = db.OpenRecordset("qryUnassignedNewLeads", dbOpenDynaset)
SumLeads = DSum("CountOfSource", "qryUnassignedNewLeads")
Do Until rs.EOF

Percent = rs.Fields("CountOfSource") / SumLeads
NumLeads = CInt(Percent * rs.Fields("CountOfSource"))
CurrentSource = rs.Fields("Source")
DoCmd.RunSQL "UPDATE clients SET salesperson = " & Text15 & " WHERE ID
IN
( SELECT TOP " & NumLeads & " ID FROM clients WHERE Salesperson IS NULL
And
[task note] = 'Unworked Lead' And Source = " & CurrentSource & " )"

rs.MoveNext
Loop

Percent seems to be calculating correctly but NumLeads keeps getting set
to
zero. I am also having a problem with setting CurrentSource, that rs
command
doesn't seem to be working.

DSum("CountOfSource", "qryUnassignedNewLeads") should sum all the values
under countOfSource for the said saved query.

As for the syntax goes, seems you need rs.Fields("CountOfSource") rather
than just CountOfSource, in the two lines following Do Until rs.EOF

As for the update, it seems you are updating MANY records, not just one,
and
you don't specify an ORDER BY, which is highly un-usual, when using a
SELECT TOP.

Hoping it may help,
Vanderghast, Access MVP

Here is the code I have so far:

[quoted text clipped - 27 lines]

several
counts, a SQL query??

 

[jbiggs via AccessMonster.com]

Percent and NumLeads are calculating correctly now, but my update is not
working - NumLeads will be set to 1 but 0 rows will update. Here is the code:


CurrentSource = RS.Fields("Source")

DoCmd.RunSQL "UPDATE clients SET salesperson = " & Text15 & " WHERE ID IN (
SELECT TOP " & NumLeads & " ID FROM clients WHERE Salesperson IS NULL And
[task note] = 'Unworked Lead' And Source = "" & CurrentSource & "" )"

CurrentSource will be equal to "Test New" - am I wrong on the syntax?

How have you declared the variable Percent? If you declared it as integer,
then, indeed, it will truncate the percentage (a value between 0 and 1) to
0. If Percent is zero, then NumLeads will also.

I don't understand what you mean by "CurrentSource", given the context. Is
it rs.Fields("Source") ? Is it a number or an alphanumeric string? If it
is alpha, you will need to add the appropriate delimiter, in the text of the
SQL command you are building.

I still find your "UPDATE ... IN (SELECT TOP ... )" quite out of the
ordinary (updating multiple records, and no ORDER BY in the sub-query).

Vanderghast, Access MVP

Current code:

[quoted text clipped - 40 lines]

 

[Michel Walsh]

I have no idea about what your update statement does, neither about what is
your intention by doing it.


UPDATE clients
SET salesperson = 15
WHERE ID IN( SELECT TOP 5 ID
FROM clients
WHERE salesperson IS NULL
AND [task note]='Unworked Lead'
AND source = 'test new' )




assuming that text15 hold the value 15 and that NumLeads hold the value 5


All records with an ID value IN the values returned by



SELECT TOP 5 ID
FROM clients
WHERE salesperson IS NULL
AND [task note]='Unworked Lead'
AND source = 'test new'



would have their salesperson value updated, but what are those ID returned
by that last query? Are they those you really think they are? It seems
they are not.



Vanderghast, Access MVP

Percent and NumLeads are calculating correctly now, but my update is not
working - NumLeads will be set to 1 but 0 rows will update. Here is the
code:


CurrentSource = RS.Fields("Source")

DoCmd.RunSQL "UPDATE clients SET salesperson = " & Text15 & " WHERE ID IN
(
SELECT TOP " & NumLeads & " ID FROM clients WHERE Salesperson IS NULL And
[task note] = 'Unworked Lead' And Source = "" & CurrentSource & "" )"

CurrentSource will be equal to "Test New" - am I wrong on the syntax?

How have you declared the variable Percent? If you declared it as
integer,
then, indeed, it will truncate the percentage (a value between 0 and 1) to
0. If Percent is zero, then NumLeads will also.

I don't understand what you mean by "CurrentSource", given the context.
Is
it rs.Fields("Source") ? Is it a number or an alphanumeric string? If
it
is alpha, you will need to add the appropriate delimiter, in the text of
the
SQL command you are building.

I still find your "UPDATE ... IN (SELECT TOP ... )" quite out of the
ordinary (updating multiple records, and no ORDER BY in the sub-query).

Vanderghast, Access MVP

Current code:

[quoted text clipped - 40 lines]

several
counts, a SQL query??