Output 10% of Records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that contains employee ids and routes that they have closed.
I need a way to have a query output 10% of the records per employee id. Any
suggestions?
 
Take a look at the TOP property of queries. You can get a query running
that returns all the records, then change the TOP property to return only
10%.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Thanks, but tried that already. Problem is that it gives me 10% of the total
records not 10% of records for each Emp ID. Right now it's being done in
Excel manually by counting how many routes each id has and selecting 10% of
the records for id which is then pasted into another sheet. I know there is
an easier way.
 
You need to help us help you.

Perhaps by posting the query that you do have that gets 10% of the total
records.

Generically, you need to use a subquery to identify the records you
want. The query might look something like the following.

SELECT EmployeeId, RouteID
FROM SomeTable
WHERE RouteID in
(SELECT Top 10 PERCENT RouteID
FROM SomeTable as T
WHERE T.EmployeeID = SomeTable.EmployeeID
And T.Status = "Closed"
ORDER BY RouteID)

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Hi John. Here it is:

SELECT TOP 10 PERCENT Chadd1.RouteCompletedBy, Chadd1.RouteCompletedByDesc,
Chadd1.ID, Chadd1.[Loan Number], Chadd1.DateReceived, Chadd1.[Audit Date],
Chadd1.AuditCompletedBy, Chadd1.AuditCompletedByDesc, Chadd1.[Route
Completed], Chadd1.CompletedDepartmentDesc, Chadd1.[Request Type],
Chadd1.Comments, Chadd1.Pass, Chadd1.Status, Chadd1.Count, Chadd1.[Employee
Name], Chadd1.Workgroup, Chadd1.Department, Chadd1.Team, Chadd1.Site,
Chadd1.Product FROM Chadd1;
 
Hey, John! It worked. I tried using the subquery idea and it worked
fantastic!! Thank you! I was overthinking the problem and couldn't see how
simple it was!!
 
Back
Top