"Random" assignment

[Rebecca]

I am working on a query in the hopes of producing
a "randomly" assigned field. It goes like this: In
Table1 I have account information. In Table2 I have a
list of sales reps. What I want is to enter in the
account information and have a field for SalesRep that
populates either with a randomly selected rep from the
list, or to go through the list of reps 1, 2, 3, so the
distribution is even as possible.

Thanks.

 

[Dejan]

THis will return random single record from a table:

If sales reps are assigned NUMERIC UNIQUE SalesRepID:
SELECT TOP 1 * FROM tblSalesReps ORDER BY Rnd(SalesRepID)

If you have to use non numeric field, try this:
SELECT TOP 1 * FROM tblSalesReps ORDER BY Rnd(1 + Len
([SalesRepName} & ""))

This I learned from someone named Gary Walter, posted on
this forum 12/31/2003 3:49:18 AM