Retrieving Random Records(with a S)

[2176]

I have formed a database for the purpose of auditing different files that we are in control of. There are 25000 individual books in need of auditing, but each month only 15% of them need to be audited. I need a instructions on setting this where it will return 15% of these books randomly. Basically I want to be able to hit a button that says audit, and get a report that has the listing of these randomly picked books.
All help will be well appreciated!
Thanx
Toya

 

[AWithers]

something like this should do the trick


SELECT TOP 15 PERCENT bookid
FROM yourtable
ORDER BY Rnd([bookid]);


Andy

 

[2176]

AWithers Your help was awesome..I do have 1 question though
I actually have this set up as a parent and child arrangement. I have the "Mainform" which lists the File information such as File number, monitors etc...and then the subform that list the books # and quantity. So there are actually 2 tables that I combined into 1 query. When I added that function it picked random ID numbers but the rest of the information was like this NAME?. I understand why its doing that, but I'd rather not combine this tables into one...My question is this in the function can I draw from the query instead of the table??

 

[AWithers]

You can use a query similar to this on any data set where you have a numerical field on which you can use the random (RND()) function

Andy

 

[MichaelRed]

On the other hand, if you need to audit all of the books within a given time span, the above will almost certainly fail. Auditing 15% per month should get all items over ~ 7 month period, however using a random selection will generate some number of duplications within the 'Top 15 Percent' for each iteration/usage. You would need to add a field to hold the last audit date, and exclude any books which have been audited within the period of interest (seven months?). Doing this will alter/complicate the select query, as 15% of the filterd record set will decrease over time and eventually be negligible.

The details here are -as usual- incomplete, however if there is a specific number (6 or 7) of intervals (months) over which the entire set should be audited, and the aduditing should be truly random, you will need to do a bit more than simply selecting a random group of records. A FEW items to consider:

An indiciator (date or flag) needs to be included to determine which items are available to audit.

The selection process should account for ALL items over the audit span (6 or 7 months), but SHOULD it allow duplicate audits within the span?

If the audits are really supposed to be random, the process cannot include the criteria that all items be audited within the span. Which criteria takes precedence (all within the span -or- randomly)? Note that 'all within the span' will FORCE some items to be audited in the last interval, and sucessively increase the probaility of an audit with each interval within the span until an audit of the item is accomplished within the span.

The above may appear to be 'obvious', however I do not see any discussion addressing this - and it is an important issue with respect to the posted discussion.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over