Select records by employee
in [Queries]
|
From: Mark.M.Sweeney on 1 Jul 2008 16:22 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
From: KARL DEWEY on 1 Jul 2008 18:19 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 "Mark.M.Sweeney(a)gmail.com" wrote: > 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 >
From: Mark.M.Sweeney on 1 Jul 2008 19:23 On Jul 1, 6:19 pm, KARL DEWEY <KARLDE...(a)discussions.microsoft.com> wrote: > 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 > > > > "Mark.M.Swee...(a)gmail.com" wrote: > > 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?
From: John Spencer on 2 Jul 2008 08:14 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 Mark.M.Sweeney(a)gmail.com wrote: > 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
|
Pages: 1 Prev: Case or IIF? Next: Calculating Working Hours that cross over midnight |