If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Select records by employee
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 |
#2
|
|||
|
|||
Select records by employee
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 " 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 |
#3
|
|||
|
|||
Select records by employee
On Jul 1, 6:19*pm, KARL DEWEY
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 " 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? |
#4
|
|||
|
|||
Select records by employee
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 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 |
Thread Tools | |
Display Modes | |
|
|