CODEWITHSUNDEEP
sqlvbams-accessrandom
artemis
artemis

Reputation: 7281

Randomize Return Results - Access

I need to match up an employee with a task in a small Microsoft Access DB I built. Essentially, I have a list of 45 potential tasks, and I have 25 employees. What I need is:

  1. Each employee to have at LEAST one task
  2. No employee to have more than TWO
  3. Be able to randomize the results every time I run the query (so the same people don't get consistently the same tasks)

My table structure is: Employees - w/ fields: ID, Name Tasks - w/ fields: ID, Location, Task Group, Task

I know this is a dumb question, but I truly am struggling. I have searched through SO and Google for help but have been unsuccessful.

I don't have a way to link together employees to tasks since each employee is capable of every task, so I was going to:

 1. SELECT * from Employees
 2. SELECT * from Tasks
 3. Union
 4. COUNT(Name) <= 2

But I don't know how to randomize those results so that folks are randomly matched up, with each person at least once and nobody more than twice.

Any help or guidance is appreciated. Thank you.

Upvotes: 1

Views: 65

Answers (1)

Parfait
Parfait

Reputation: 107687

Consider a cross join with an aggregate query that randomizes the choice set. Currently, at 45 X 25 this yields a cartesian product of 1,125 records which is manageable.

Select query (save as a query object, assumes Tasks has autonumber field)

SELECT cj.[Emp_Name], Max(cj.ID) As M_ID, Max(cj.Task) As M_Task
FROM
   (SELECT e.[Emp_Name], t.ID, t.Task
    FROM Employees e,
         Tasks t) cj
GROUP BY cj.[Emp_Name], Rnd(cj.ID)
ORDER BY cj.[Emp_Name], Rnd(cj.ID)

However, the challenge here is this above query randomizes the order of all 45 tasks per each of the 25 employees whereas you need the top two tasks per employee. Unfortunately, MS Access does not have a row id like other DBMS to use to select top 2 per employee. And we cannot use a correlated subquery on Task ID per Employee since this will always return the highest two task IDs by their value and not random top two IDs.

Therefore to do so in Access, you will need a temp table regularly cleaned out prior to each allocation of employee tasks and use autonumber for selection via correlated subquery.

Create table (run once, autonumber field required)

CREATE TABLE CrossJoinRandomPicks (
   ID AUTOINCREMENT PRIMARY KEY,
   Emp_Name TEXT(255),
   M_ID LONG,
   M_Task TEXT(255)
)

Delete query (run regularly)

DELETE FROM CrossJoinRandomPicks;

Append query (run regularly)

INSERT INTO CrossJoinRandomPicks ([Emp_Name], [M_ID], [M_Task])
SELECT [Emp_Name], [M_ID], [M_Task] 
FROM mySavedCrossJoinQuery;

Final query (selects top two random tasks for each employee)

SELECT c.name, c.M_Letter
FROM CrossJoinRandomPicks c
WHERE 
  (SELECT Count(*) FROM CrossJoinRandomPicks sub
   WHERE sub.name = c.name
     AND sub.ID <= c.ID) <= 2;

Upvotes: 1

Related Questions