Output 10% of Records

C

CIU2310

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?
 
J

Jeff Boyce

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
 
C

CIU2310

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.
 
J

John Spencer

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
'====================================================
 
C

CIU2310

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;
 
C

CIU2310

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!!
 
Top