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
|
|||
|
|||
Random Sample Within Parameters
Hoping someone can help. I have reviewed many postings on drawing a random
sample and have looked at several web pages, but the postings all assume a higher Access knowledge level than I have. I have created a query from a table from which I need to select a sample of 5 [strReviewID] records from a defined time period (one week) and by a selected employee ID. The query is presently set up as follows: SELECT tblReview.strReviewID, tblReview.CloseDate, tblReview.ReviewType, tblReview.strAssigned, tblReview.Probe, tblReview.Targeted, tblReview.QAReviewer, tblReview.QADate FROM tblReview WHERE(((tblReview.CloseDate) Between [Enter begin date] And [end date]) AND ((tblReview.strAssigned)=[Enter Employee ID])); These fields are either alphanumeric set to text or they are a date. Where do I place the "Rnd" statement and how should it be set up? Another point to remember is that I want a maximum of 5 records to review per employee, but not all employees may have 5 records available. -- Thanks! |
#2
|
|||
|
|||
Random Sample Within Parameters
This will do what you asked --
SELECT TOP 5 tblReview.strReviewID, tblReview.CloseDate, tblReview.ReviewType, tblReview.strAssigned, tblReview.Probe, tblReview.Targeted, tblReview.QAReviewer, tblReview.QADate, Int((([strReviewID]*3.41423456)-Int([strReviewID]*3.41423456))*[Enter a 2 digit number]) AS Rnd FROM tblReview WHERE (((tblReview.CloseDate) Between [Enter begin date] And [end date]) AND ((tblReview.strAssigned)=[Enter Employee ID])) ORDER BY Int((([strReviewID]*3.41423456)-Int([strReviewID]*3.41423456))*[Enter a 2 digit number]); "Don" wrote: Hoping someone can help. I have reviewed many postings on drawing a random sample and have looked at several web pages, but the postings all assume a higher Access knowledge level than I have. I have created a query from a table from which I need to select a sample of 5 [strReviewID] records from a defined time period (one week) and by a selected employee ID. The query is presently set up as follows: SELECT tblReview.strReviewID, tblReview.CloseDate, tblReview.ReviewType, tblReview.strAssigned, tblReview.Probe, tblReview.Targeted, tblReview.QAReviewer, tblReview.QADate FROM tblReview WHERE(((tblReview.CloseDate) Between [Enter begin date] And [end date]) AND ((tblReview.strAssigned)=[Enter Employee ID])); These fields are either alphanumeric set to text or they are a date. Where do I place the "Rnd" statement and how should it be set up? Another point to remember is that I want a maximum of 5 records to review per employee, but not all employees may have 5 records available. -- Thanks! |
Thread Tools | |
Display Modes | |
|
|