Update oldest first

[jbiggs via AccessMonster.com]

I have an update query that is assigning leads to salespeople based on what
percentage of leads are available for each source, that query looks like:

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 & """) "

The issue is, the leads are assigned randomly and we have a bunch of old
leads backed up in the system. I tried to add ORDER BY [System Date] DESC at
the end of the query but it made things go haywire. Where do I need to add
the ORDER BY statement?

 

[Jeff Boyce]

One way to "discover" this would be to copy the existing SQL statement into
a new query, then use the query design view to modify the sort order, before
returning to SQL view to see how Access converts it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[jbiggs via AccessMonster.com]

Hi Jeff,

I gave this a try but the problem is that it only creates 2 columns in design
view, one for the salesperson it is updated to and then the other column
contains the entire subquery.

One way to "discover" this would be to copy the existing SQL statement into
a new query, then use the query design view to modify the sort order, before
returning to SQL view to see how Access converts it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have an update query that is assigning leads to salespeople based on what
percentage of leads are available for each source, that query looks like:

[quoted text clipped - 10 lines]

add
the ORDER BY statement?