Randomly picking records 1

[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

 

[cascot]

Try this.

In your table create a new field of type Single (Number) and call it something like TmpRandNbr.

Create a code module and in it create a public function with this code:

(this code assumes the table is called "Book", the unique key of that table is called "BookId", and that the new field is called "TmpRandNbr". It also assumes you are using DAO)

Dim db As Database
Dim rs As Recordset
Dim SQLcmd As String

Set db = CurrentDb

SQLcmd = "SELECT * FROM Book ORDER BY BookId"

Set rs = db.OpenRecordset(SQLcmd, dbOpenDynaset)

With rs
Do Until .EOF
.Edit
!TmpRandNbr = Rnd()
.Update
.MoveNext
Loop
.Close
End With
Set rs = Nothing

You then also need to set up a select query containing whichever field(s) you need to identify the selected books, plus the newly added "TmpRandNbr" field with an ascending sort order on the TmpRandNbr field. If you then get the properties of the query to display, you should see a property setting called "Top Values". Enter "15%" into that property box (excluding the double quote marks).

In a macro you then need to call the public function followed by the select query. This will set the TmpRandNbr field against each book record then return the top 15% in ascending order of random number.

Hope this helps.

 

[2176]

Cascot if you are out there!!
Ok, I copied and pasted the module in and added the TempRndNbr to my table. I also created a select query adding the fields that were needed. When I went to compile the module though it gave me a "Compile Error: Invaild outside procedure" It errored in the spot: Setdb = CurrentDb. With the error on CurrentDb. I changed all the nessesary things like the table name and the primary key name....What did I do wrong??

 

[cascot]

Do you have a reference set up within your .mdb file to Microsoft DAO 3.6 Object Library (if Access 2000), or DAO 3.50 or 3.51 (if Access 97)?

You can check this by viewing a code module in design view and dropping dow the Tools menu and selecting References.

If that is present check the line reads

Set db = CurrentDb()

and that db is declared PRIOR to the above line, using the line

Dim db As Database

ensure you have spaces in the correct places.

If that still does not work, let me know which version of Microsoft Access you are running (a question I ought to have asked earlier)?

 

[2176]

Cascot,

I'm sorry I keep bothering you but it's still not working. I'm using Microsoft Access 2000 Professional. I just might not be doing this right...I'm clicking on the Module Button and pasting the information you've given me into it. And it's giving me the same error. I did check the correct reference for Access 2000. Any and all help is so appreciated.
Thankyou

 

[StarPassing]

You should also take a look at:

http://www.mvps.org/access/queries/qry0011.htm

 

[cascot]

2176,

I was being silly by brushing over the error message. What you are doing is pasting my code into a module when, as I said in my first message, you need to first create a public function within the code module. To achieve this, with the code module in design view, drop down the "Insert" menu and choose "Procedure". In the Name box type "SetTmpRandNbr", in the Type section choose "Function" and in Scope choose "Public", then click the "OK" button. You can then paste my example code in between the line beginning "Public Function" and the line beginning "End".

You then need to create a macro which will firstly run this function (SetTmpRandNbr) then run the select query discussed in my first post.

Hope that helps.

Let me know.

 

[2176]

Cascot,
Hopefully 3 last things. When the Macro was ran there was a compiling error in the module - - .Edit; error stated method or data member not found... Secondly, now I'am a first timer with some of this stuff (I'm sure you could tell ) I understand how to get the marco to run the module, but how do you tie in the query?? And thirdly what is the TempRandNbr in the field going to do?? As I typed in the book information in that field appeared a 0. Should I make a new table and make it the primary key with a autonumber and then add it to the book table and then tie it together in the relationship menu?? I'm just a little confused. But thankyou for your extreme patience.

 

[cascot]

2176,

Let's work through a few things and see if we can get to the root cause of the problem...

01. A complaint about the .Edit would seem to suggest a problem with the references to the database objects library. Probably that you do not have a reference to DAO, only ADO (the default).

You can check this out by highlighting a code module, clicking the "Design" button, then dropping down the "Tools" menu and selecting "References". Look down the list of ticked references (there are likely to be about 4 of them). Ensure there is one called "Microsoft DAO 3.6 Object Library". If there is not, scroll down the list until you see it and check the box opposite it. If the list of ticked references includes one called "Microsoft ActiveX Data Object Library..." UNcheck this one. Then click the OK button.

Getting the macro to run the query involves nothing more than adding a second comand to the same macro as the one calling the code funtion. The macro action is "OpenQuery", you then specify the query name. It would also be wise to display the query datasheet in read only mode.

If you want more detail on this part (or any other let me know).

Your 3rd point...

The field should be called "TmpRandNbr", not "Temp...". The field will by default be given a value of 0 (zero) when a new record is created (unless you changed the default to be something else). When the function is run it will loop through every record in the table and enter a random number between 0 and 1 into the TmpRandNbr field. The query, which gets run subsequent to the code function, orders the records in ascending order according to the value in TmpRandNbr and displays the top 15% of the list.

Hope this helps, but let me know either way. I'll be only to glad to help, whether it be more problem solving or guidance or info any any aspect.

 

[2176]

Thankyou so much it worked perfect!! Really I can't thank you enough. I appreciate this last week you have spent helping me to improve this project...Many blessings to you.

 

[cascot]

2176,

I'm glad I could be of help. It's what we're all here for after all - to help one another as much as we can.

Best of luck as your project continues.