Query for Random Sample with Multiple Criteria

[Hudas]

Hi Everyone,

I am hoping someone could help me with my problem because I'm completely stuck. I've search the web but I can't find the answer. So here it goes...

I have table with [EmployeeName] and [DateReceived] column what I am hoping to accomplish is to get 5 samples of each Employee for each Date received.

I would greatly appreciate any help.

Thank you
Hudas

 

[CJ_London]

You'll need something like this pseudo code to get you started - Note that you need a unique ID in your data to make it work.

SELECT * 
FROM myTable 
WHERE ID in (SELECT Top 5 ID from myTable as tmp WHERE EmployeeName=myTable.EmployeeName and DateReceived=myTable.DateReceived)

 

[Hudas]

Thank you very much... CJ_London

I've tried your query and its getting the top 5 Employee name for each date. I'm hoping that for each employee I will have 5 samples for each date

 

[CJ_London]

That code should produce 5 records for each employee so suggests there is something else.

please post some sample data from your table plus the query as you have written it

 

[Hudas]

Here is the code in the SQL view of the query.

SELECT *
FROM tbl_Sent_Email
WHERE (((tbl_Sent_Email.ID) In (SELECT Top 5 ID from tbl_Sent_Email as tmp WHERE tbl_Sent_Email.UseriD and ReceivedDate=tbl_Sent_Email.ReceivedDate)));

ID is an Autonumber column
UserdiD is name of the Employees
ReceivedDate is a Date

I've attached sample data from the table

 

[CJ_London]

you've missed a bit of the subquery out

.... WHERE tbl_Sent_Email.UseriD ....

should be

.... WHERE UserID=tbl_Sent_Email.UseriD ....