HELP taking every 10 record

[johnawebbhsd]

I found this on here before and used it, somehow my DB got corrupted and lost the code.

I have a query(A) that I would like to run another query(B) on (append-table) to take a 10% sample from (A). So for example if I have 2000 records in (A) I would like query(B) to take 200 records (random) and append that to a table. As I said I found this on here but have looked all day and can't find it again.

I do think that it took every 10th record is how it got 10% of the table. If anyone knows how to do this or where that topic may be please advice. Thanks

 

[CT Witter]

I believe this is what you are looking for. MS KB article: 208855 - Find N Records in Random Order

http://support.microsoft.com/default.aspx?scid=kb;EN-US;208855

HTH,
CT

 

[johnawebbhsd]

Thanks I will try that.. I don't think this is how I accomplished this task before.. Guess I will keep better notes in the future.

It seems like I will have to say to pull a random 200 vs. I think before I was saying to pull every 10th record.... I will start to play with this and see what I can come up with..

 

[CT Witter]

I guess it all depends on how random you need your random sample to be. I would say that every 10th item is not very random ie I as a human can reliably and accurately predict what your table will look like.

Plus, it depends on your table makeup. Taking a random 10% or every 10th record for this case(2000) is equivalent (200 records).

But depending on the total number of records you can different resulting recordsets.
For example,
10% of 22 records - Access rounds up and you get three (3) records.
every 10th of 22 records - 2 records total

******************************************************

Here is another article that shows how to get every Nth record.

http://support.microsoft.com/default.aspx?scid=kb;en-us;199679&Product=acc2000 - How to Simulate a Dynamic Counter in a Table or a Query to Count Records

HTH,
CT

 

[johnawebbhsd]

Ok... sorry to be dense on this but I can't figure it out... I went to the link you provided.... took the code of
Function test()
?CreateCountTbl("MSG_RET_w_ACC_Final","new table",10,False)
end function

I get an error when try to run Method not valid w/o suitable object. I did select Microsoft DAO 3.6 Object Library....

I also tried running the long code it did nothing... I am but still trying.... I wish I kept a copy of the code from before.. won't make that mistake again... It was very short and sweet I thought... ;-(

 

[CT Witter]

change your:

Function test()
?CreateCountTbl("MSG_RET_w_ACC_Final","new table",10,False)
end function

to

Function test()
Call CreateCountTbl("MSG_RET_w_ACC_Final","new table",10,False)
end function


The ? notation is for use in the immediate window
HTH,
CT

 

[johnawebbhsd]

Oh OK. I am still learning a lot. I did this and seems to be working OK. This is differenat than how it was working before. But it works still the same. If I ever find the old code I will post it here. Thanks for all your help and for bearing with me.

I am not sure what happened to my original database, but it seems to have lost a lot of code and joins were all messed up too.

thanks again