creating worklist by alpha then count

[random]

I have four end users on an 2003 DB. I need to create worklist by alpha split
then count. Based on number of accounts for the day the alpha may vary.
Bottom line, I'm attempting to spread the work load evenly, but ensure
everything is done. The data is streaming from an interface link where the
account are created/scheuduled. This data is imported into the DB. I first
created a query with Mod 4+1, but this does not sort by alpha then divide the
count. I want to take 100 records a-h are the first 25 assign to user #1.
Next: I-K, take 25 assign to user #2... etc

Is there any way to accomplish this? The problem is that you cannot set a
specific alpha range or count as it may vary based on the name of clients and
number for that specific date.

Thanks

 

[John Spencer]

SELECT Top 25 Percent *
FROM TableA
ORDER BY AccountName, PrimaryKeyField

SELECT Top 25 Percent *
FROM TableA
WHERE PrimaryKeyField Not In
(SELECT Top 25 Percent *
FROM TableA
ORDER BY AccountName, PrimaryKeyField)
ORDER BY AccountName, PrimaryKeyField

Here is where you may get erroneous result

SELECT Top 25 Percent *
FROM TableA
WHERE PrimaryKeyField Not In
(SELECT Top 50 Percent *
FROM TableA
ORDER BY AccountName, PrimaryKeyField)
ORDER BY AccountName, PrimaryKeyField


SELECT Top 25 Percent *
FROM TableA
WHERE PrimaryKeyField Not In
(SELECT Top 75 Percent *
FROM TableA
ORDER BY AccountName, PrimaryKeyField)
ORDER BY AccountName, PrimaryKeyField

Of course, if you have a field that you use to assing the worker to,
then life is a bit easier. You assign the top 25 percent to worker A,
33 percent of the remainder to worker B, 50 percent of the remainder to
worker C, and the remainder to worker D


Update TableA
AssignedTo = "WorkerA"
WHERE AccountName in
(SELECT Top 25 Percent AccountName
FROM Table A
WHERE AssignedTo is Null
ORDER BY AccountName)

Then run 33 percent, 50 percent and 100 percent.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================