Select records by employee

[Mark.M.Sweeney]

I have a table with about 31000 records. Table as the following
fields:
Invoice Number,
Supervisor,
Employee,
Action

The action only has 3 possible variables - buy, sell, hold.

I want to check the quality of the employees work an so I want to
somehow query and randomly select:

10 buy, 10 sell and 5 hold for each employee, by supervisor.

I've tried the below, but it is way too limiting as I'm looking for a
result of a lot of files, not just 10:

SELECT TOP 5 INVOICE_NUMBER, SUPERVISOR, EMPLOYEE, ACTION
FROM EMPLOYEE_STATS
WHERE (((ACTION)=[Action - BUY, SELL, HOLD?]))
ORDER BY SUPERVISOR, EMPLOYEE, ACTION;

In the above, I put in the WHERE clause so that I can get 10 per
action. Ideally, I wouldn't have that because I want 10, 10 and 5 as
stated above for each employee.

Any help is hugely appreciated!

Mark

 

[KARL DEWEY]

Try this ---
SELECT Q.Supervisor, Q.Employee, Q.Action, (SELECT COUNT(*) FROM M_Mark Q1
WHERE Q1.[Supervisor] = Q.[Supervisor]
AND Q1.[Employee] = Q.[Employee]
AND Q1.[Action] = Q.[Action]
AND Q1.[Invoice Number] < Q.[Invoice Number])+1 AS Rank
FROM M_Mark AS Q
WHERE ((((SELECT COUNT(*) FROM M_Mark Q1
WHERE Q1.[Supervisor] = Q.[Supervisor]
AND Q1.[Employee] = Q.[Employee]
AND Q1.[Action] = Q.[Action]
AND Q1.[Invoice Number] < Q.[Invoice
Number])+1)<=IIf([Action]="Hold",5,10)))
ORDER BY Q.Supervisor, Q.Employee, Q.Action;

 

[Mark.M.Sweeney]

Try this ---
SELECT Q.Supervisor, Q.Employee, Q.Action, (SELECT COUNT(*) FROM M_Mark Q1
      WHERE Q1.[Supervisor] = Q.[Supervisor]
        AND  Q1.[Employee] = Q.[Employee]
        AND Q1.[Action] = Q.[Action]
        AND Q1.[Invoice Number] < Q.[Invoice Number])+1 AS Rank
FROM M_Mark AS Q
WHERE ((((SELECT COUNT(*) FROM M_Mark Q1
      WHERE Q1.[Supervisor] = Q.[Supervisor]
        AND  Q1.[Employee] = Q.[Employee]
        AND Q1.[Action] = Q.[Action]
        AND Q1.[Invoice Number] < Q.[Invoice
Number])+1)<=IIf([Action]="Hold",5,10)))
ORDER BY Q.Supervisor, Q.Employee, Q.Action;

--
KARL DEWEY
Build a little - Test a little

I have a table with about 31000 records.  Table as the following
fields:
Invoice Number,
Supervisor,
Employee,
Action

The action only has 3 possible variables - buy, sell, hold.

I want to check the quality of the employees work an so I want to
somehow query and randomly select:

10 buy, 10 sell and 5 hold for each employee, by supervisor.

I've tried the below, but it is way too limiting as I'm looking for a
result of a lot of files, not just 10:

SELECT TOP 5 INVOICE_NUMBER, SUPERVISOR, EMPLOYEE, ACTION
FROM EMPLOYEE_STATS
WHERE (((ACTION)=[Action - BUY, SELL, HOLD?]))
ORDER BY SUPERVISOR, EMPLOYEE, ACTION;

In the above, I put in the WHERE clause so that I can get 10 per
action.  Ideally, I wouldn't have that because I want 10, 10 and 5 as
stated above for each employee.

Any help is hugely appreciated!

Mark- Hide quoted text -

- Show quoted text -

I tried it, but got an error "The Microsoft Jet database engine cannot
find the input table or query M_Mark. I realized that the query was
using what appears to be a table named M_Mark which is not a table in
my db. Should I change the M_Mark to the real table name of
EMPLOYEE_STATS?

When I did change to Employee_Stats, the query didn't real run, it
just got hung up. Thoughts?

 

[John Spencer]

Since you want a random selection, you will need a function to assign a random
number to each record.

See http://support.microsoft.com/default.aspx?id=208855

Possible, not quite as easy as it ought to be.

Copy and paste this little function into a VBA module; save the module as
basRandom (anything except RndNum, you can't use the same name twice);

Public Function RndNum(vIgnore as Variant) As Double
Static bRnd As Boolean
If Not bRnd Then
Randomize
bRnd = False
End If
RndNum = Rnd()
End Function

Then include a calculated field in the Query by typing:

Shuffle: RndNum([somefield])

where "somefield" is any numeric field in your table - this just forces Access
to give you a new random number for every row. If you don't have a numeric
field available then you can use RndNum(Len([SomeField])) to force a number to
be generated.

Sort by this field and it will shuffle your data into random order.

Source: John Vinson

USING the Random function will slow down the query, but I don't know of a
better method to get the results as specified.

Query to get 10 Buy
SELECT INVOICE_NUMBER, SUPERVISOR, EMPLOYEE, ACTION
FROM EMPLOYEE_STATS as EA
WHERE [INVOICE_NUMBER] In
(SELECT TOP 10 [INVOICE_NUMBER]
FROM EMPLOYEE_STATS as EB
WHERE Action = "BUY"
AND EB.Employee = EA.Employee
ORDER BY RndNum(Len(Employee))

SELECT INVOICE_NUMBER, SUPERVISOR, EMPLOYEE, ACTION
FROM EMPLOYEE_STATS as EA
WHERE [INVOICE_NUMBER] In
(SELECT TOP 10 [INVOICE_NUMBER]
FROM EMPLOYEE_STATS as EB
WHERE Action = "SELL"
AND EB.Employee = EA.Employee
ORDER BY RndNum(Len(Employee))

SELECT INVOICE_NUMBER, SUPERVISOR, EMPLOYEE, ACTION
FROM EMPLOYEE_STATS as EA
WHERE [INVOICE_NUMBER] In
(SELECT TOP 5 [INVOICE_NUMBER]
FROM EMPLOYEE_STATS as EB
WHERE Action = "Hold"
AND EB.Employee = EA.Employee
ORDER BY RndNum(Len(Employee))

Now to return 10,10, and 5 Create and save the three queries and then union
the three queries using a UNION ALL query.

SELECT INVOICE_NUMBER, SUPERVISOR, EMPLOYEE, ACTION
FROM QueryBuy
UNION ALL
SELECT INVOICE_NUMBER, SUPERVISOR, EMPLOYEE, ACTION
FROM QuerySell
UNION ALL
SELECT INVOICE_NUMBER, SUPERVISOR, EMPLOYEE, ACTION
FROM QueryHold
SORT BY SUPERVISOR, EMPLOYEE, ACTION, INVOICE_NUMBER

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have a table with about 31000 records. Table as the following
fields:
Invoice Number,
Supervisor,
Employee,
Action

The action only has 3 possible variables - buy, sell, hold.

I want to check the quality of the employees work an so I want to
somehow query and randomly select:

10 buy, 10 sell and 5 hold for each employee, by supervisor.

I've tried the below, but it is way too limiting as I'm looking for a
result of a lot of files, not just 10:

SELECT TOP 5 INVOICE_NUMBER, SUPERVISOR, EMPLOYEE, ACTION
FROM EMPLOYEE_STATS
WHERE (((ACTION)=[Action - BUY, SELL, HOLD?]))
ORDER BY SUPERVISOR, EMPLOYEE, ACTION;

In the above, I put in the WHERE clause so that I can get 10 per
action. Ideally, I wouldn't have that because I want 10, 10 and 5 as
stated above for each employee.

Any help is hugely appreciated!

Mark