Update top x records

[jbiggs via AccessMonster.com]

I am trying to write a query that will assign x number of leads to a certain
sales rep. I am not sure about the best way to accomplish this, should I
save a top 1 query and then loop through or imbed a select statement in my
update query?

I have been trying to use something like:

DoCmd.RunSQL "UPDATE clients SET salesperson = " & [sales rep name] & " WHERE
IN ( SELECT TOP " & Howmany & " FROM clients " & " WHERE Salesperson IS
NULL And Source = [leadsource] )"

With no success thus far.

 

[Baz]

It would help if you explained what happens when you attempt to run this.

I can see several obvious problems:

1. Assuming that [sales rep name] is a text field, you need to enclose it in
quote marks
2. You haven't said *what* needs to be IN the subbquery
3. Assuming that Howmany is merely a number, there is no SELECT list in the
subquery.
4. Where does [leadsource] come from?

You possibly need a query which looks something like this (replace
some_field with the appropriate identifier(s)):

DoCmd.RunSQL "UPDATE clients SET salesperson = """ & [sales rep name] & """
WHERE
some_field IN ( SELECT TOP " & Howmany & " some_field FROM clients WHERE
Salesperson IS
NULL And Source = """ & [leadsource] & """ )"

 

[jbiggs via AccessMonster.com]

1) Sales rep name is a text field - Thanks.
2) I am not sure what needs to be there - I will define the fields below.
3) See below.
4)[leadsource] comes from a table

Relevant fields

[Howmany] - Number from a form, tells how many leads to assign.
[Sales rep name] - Text field from table - tells who to assign leads to.
[leadsource] - Field from table that tells which type of lead to assign.

The clients table does have an ID primary key but I was not sure if that was
what needed to be used for the IN statement.

It would help if you explained what happens when you attempt to run this.

I can see several obvious problems:

1. Assuming that [sales rep name] is a text field, you need to enclose it in
quote marks
2. You haven't said *what* needs to be IN the subbquery
3. Assuming that Howmany is merely a number, there is no SELECT list in the
subquery.
4. Where does [leadsource] come from?

You possibly need a query which looks something like this (replace
some_field with the appropriate identifier(s)):

DoCmd.RunSQL "UPDATE clients SET salesperson = """ & [sales rep name] & """
WHERE
some_field IN ( SELECT TOP " & Howmany & " some_field FROM clients WHERE
Salesperson IS
NULL And Source = """ & [leadsource] & """ )"

I am trying to write a query that will assign x number of leads to a certain
sales rep. I am not sure about the best way to accomplish this, should I

[quoted text clipped - 8 lines]

With no success thus far.

 

[Baz]

My guess is that some_field needs to be whatever field is the primary key on
the clients table.

I'm afraid that merely saying that leadsource comes from a table doesn't
help. What table? How is it related to the tables used in the query? How
is the query supposed to "know" what value for leadsource you want it to
use?

1) Sales rep name is a text field - Thanks.
2) I am not sure what needs to be there - I will define the fields below.
3) See below.
4)[leadsource] comes from a table

Relevant fields

[Howmany] - Number from a form, tells how many leads to assign.
[Sales rep name] - Text field from table - tells who to assign leads to.
[leadsource] - Field from table that tells which type of lead to assign.

The clients table does have an ID primary key but I was not sure if that was
what needed to be used for the IN statement.

It would help if you explained what happens when you attempt to run this.

I can see several obvious problems:

1. Assuming that [sales rep name] is a text field, you need to enclose it in
quote marks
2. You haven't said *what* needs to be IN the subbquery
3. Assuming that Howmany is merely a number, there is no SELECT list in the
subquery.
4. Where does [leadsource] come from?

You possibly need a query which looks something like this (replace
some_field with the appropriate identifier(s)):

DoCmd.RunSQL "UPDATE clients SET salesperson = """ & [sales rep name] & """
WHERE
some_field IN ( SELECT TOP " & Howmany & " some_field FROM clients WHERE
Salesperson IS
NULL And Source = """ & [leadsource] & """ )"

I am trying to write a query that will assign x number of leads to a certain
sales rep. I am not sure about the best way to accomplish this, should

I
[quoted text clipped - 8 lines]

With no success thus far.

 

[jbiggs via AccessMonster.com]

This query is an on-click from a form. The form allows the user to select:

Sales rep, leadsource and set how many leads to assign.

My guess is that some_field needs to be whatever field is the primary key on
the clients table.

I'm afraid that merely saying that leadsource comes from a table doesn't
help. What table? How is it related to the tables used in the query? How
is the query supposed to "know" what value for leadsource you want it to
use?

1) Sales rep name is a text field - Thanks.
2) I am not sure what needs to be there - I will define the fields below.

[quoted text clipped - 35 lines]

 

[jbiggs via AccessMonster.com]

It is running using using:

DoCmd.RunSQL "UPDATE clients SET salesperson = """ & [sales rep name] & """
WHERE ID IN ( SELECT TOP " & Howmany & " ID FROM clients WHERE Salesperson
IS NULL And Source = """ & [leadsource] & """ )"

But it says it will update 0 rows every time. One this I know I need to
change: salesperson is a text field but holds a rep number. I have a
salesrep table that has both values, is there an easy way to do a lookup
within the query and assign the number instead of the name?

This query is an on-click from a form. The form allows the user to select:

Sales rep, leadsource and set how many leads to assign.

My guess is that some_field needs to be whatever field is the primary key on
the clients table.

[quoted text clipped - 9 lines]

 

[Baz]

OK, so lead source isn't from a table, it's from your form, which is what I
guessed in the first place. In which case, my original suggestion is still
the query you want, except for putting in the correct field instead of
some_field.

This query is an on-click from a form. The form allows the user to select:

Sales rep, leadsource and set how many leads to assign.

My guess is that some_field needs to be whatever field is the primary key on
the clients table.

I'm afraid that merely saying that leadsource comes from a table doesn't
help. What table? How is it related to the tables used in the query? How
is the query supposed to "know" what value for leadsource you want it to
use?

1) Sales rep name is a text field - Thanks.
2) I am not sure what needs to be there - I will define the fields

below.
[quoted text clipped - 35 lines]

With no success thus far.