Source language: Translate to:

Seclect percentage of record

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Seclect percentage of record

Postby Trainman » Tue Jun 24, 2008 12:59 pm

Can anyone help please,

I have just upgrade to DBPro and have an Access database of some 9,000 to 11,000 record (database is still growing).

What I would like to do is randomly select 5% of the records within the database and create a report that can be printed.

I can just able create the report but do not know the way to write the code to select the 5% or send this selection to the printer.

yours

Mike
Trainman
 
Posts: 113
Joined: Fri Apr 01, 2005 10:21 am
Location: England

Postby Gaev » Tue Jun 24, 2008 2:30 pm

Mike:

What is your definition of "randomly select 5% of the records within the database" ? ... e.g. record 1, 115, 200, 309, , 9000 ? ... or will it suffice to select a random range of records comprised of 5% of the records ? ... so if you have 10,000 records, can you work with 500 contiguous records starting at 1 or 9000 or 6500 etc. ?

For the first option, you might consider creating a (temporary) table with the same fields as your original Table ... then use NeoBook's Random command in a Loop/Endloop code block (0.05*[$RecCount.yourDB.yourTable] times) ... and with each iteration, copy the Random'th record to the new Table ... then perform the Reporting on the new table.

For the latter option you could consider something like ...
Code: Select all
SELECT * FROM yourTable LIMIT [randomStart],500;
... where [randomStart] could be calculated using NeoBook's Random command.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Trainman » Wed Jun 25, 2008 11:02 am

Gaev

Thank you for the help, as to the question about randomly. This is as you have quoted record 1, 115, 200,309,9000 ....

yours

Mike
Trainman
 
Posts: 113
Joined: Fri Apr 01, 2005 10:21 am
Location: England

Postby Trainman » Thu Jun 26, 2008 2:10 pm

Geav

Can you please help as I can not get the information to come up on the screen.

I have made a Loop and thing that the problem is with the Random part of the loop.
Code: Select all
:Random List
Loop "1" "500" "[Hdstone.Contacts]"
Random "(0.05*[hdStone.Contacts.$RecCount])" "[Hdstone.Rselect]"
EndLoop
Return


I think I am missing something, but my brain is not as good as yours.

yours

Mike
Trainman
 
Posts: 113
Joined: Fri Apr 01, 2005 10:21 am
Location: England

Postby beno » Thu Jun 26, 2008 4:39 pm

Hi Mike,

I do not have a clear idea on what you are trying to do, but if you would like to pick at random 5% of your database records, then my suggestions are:

- Calculate the 5% of your records, something like:

Code: Select all
Math "[MyDataBase.MyTable.$RecCount]*0.05" "0" "[FivePercent]"


Do a loop starting from 1 to [FivePercent], as in:

Code: Select all
Loop "1" "[FivePercent]" "[i]"
 
. Code to pick records

EndLoop


Now, the tricky part is inside the Loop, you have to figure out a method to pick the records in a random way. Let´s try the dbpGotoRecord action (pick a record using its sequential position, see the NBDBPro help file):

Code: Select all
Random "[MyDatabase.MyTable.$RecCount]" "[RandomRec]"
SetVar "[RandomRec]" "[RandomRec]+1"

dbpGotoRecord "MyDatabase" "MyTable" "[RandomRec]"


After this, you have a random record. The Loop will run 5% times of the total records in your database.

Now we have a possible solution for your project.

My cent.

beno
User avatar
beno
 
Posts: 678
Joined: Fri Apr 01, 2005 9:03 am
Location: México

Postby Gaev » Thu Jun 26, 2008 6:27 pm

Following up on Beno's suggestion ... the code inside the Loop/EndLoop block would look something like ...
Code: Select all
Random "[MyDatabase.MyTable.$RecCount]-1" "[RandomRec]"
SetVar "[RandomRec]" "[RandomRec]+1"

dbpGotoRecord "MyDatabase" "MyTable" "[RandomRec]"

dbpRecordToVar "MyDatabase" "MyTable" "[RecData]" "Delimiter=,;IncludeHidden=Yes;ForceQuotes=Yes"

dbpAddRecord "MyPartialDatabase" "MyPartialTable"
dbpVarToRecord "MyPartialDatabase" "MyPartialTable" "[RecData]" "Delimiter=,;IncludeHidden=Yes"
... note that ...

a) It is assumed that MyDatabase and MyTable are names of your full database/table in question ... and that MyPartialDatabase and MyPartialTable are the names of the identical database/table ... for which you did dbpCreateDatabase, dbpOpenDatabase, dbpCreateTable and dbpOpenTable ... outside of the loop ... to house the randomly selected partial records.

b) You have to subtract one from the [MyDatabase.MyTable.$RecCount] ... since the Random command counts from zero (not one)

This code will house 5% of the records of your full database/table ... selected at random from the main database/table ... in the partial database/table ... now you can run your report against this partial data set.

Please do not just copy the code snippet into your pub ... instead, study it to convince yourself that you understand what each command is meant to do ... so when something unexpected happens, you will know where to look for debugging purposes.
User avatar
Gaev
 
Posts: 3718
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Neosoft Support » Fri Jun 27, 2008 10:32 am

I found this posted on the net:

A number of other writers have suggested using a client-side cursor, loading the table and then using move by Rand()*recordcount to get a random record. This works well for small datasets or choosing just a few records, but when 20 or more records need to be selected from a large database, there can be a large performance hit.

The Rand() function in SQL seems ideal for the task, but the problem is that if used thus:

SELECT TOP 5 Questions.*, Rand() as ID
FROM Questions

the ID field always has the same number in it (ie: the Rand() was only evaluated once).

I have found an alternative method that seems to 'trick' the backend into evaluating the expression each time. It involves changing the seed for each record and is easiest to use if there is an AutoInc field in the table:

SELECT TOP 20 Questions.*
FROM Questions
ORDER BY (Rand(Questions.QID))

where QID is an AutoInc field. This does produce a random list quite easily and on my testing the list does seem to be reasonably random.

Note: Using MS Access - Rnd() is the function, not Rand()


I haven't been able to get this to work yet, but it sounds promising.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Postby Trainman » Sat Jun 28, 2008 1:57 pm

Beno
Gaev
Neosoft Support

Thank you for the area's that I need to look into and part that I need to
learn more about. I will work on this over the next week and will hope that the answer will come to me.

once again, thank you

mike
Trainman
 
Posts: 113
Joined: Fri Apr 01, 2005 10:21 am
Location: England


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 2 guests