Query - Randomly Selecting Records from Table

[LRay67]

My boss wants to extract every 9th, 18th, 27th etc. record from the table in
the database. He is utilizing this information as a verification check on
the records that have been entered into the database. Can this be done in
access and how?

 

[John Spencer]

Is there a number field that is unique in the table?

Do you have a primary key that you can use to calculate a ranking?

If you have a number field (autonumber), you can approximate every ninth
record by using criteria like

Field: AutoNumberField Mod 9
Criteria: = 0

If not then you will need to create a ranking query and use the
calculated value to get every ninth record.


SELECT TA.*
FROM Table as TA
WHERE (Select Count(*) Mod 9 FROM Table as TB WHERE TB.PrimaryKey <
TA.PrimaryKey) = 0



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[pietlinden]

Here's how...

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

 

[LRay67]

I somewhat understand the function statement, but am not an advanced
programmer in creating your own functions per say. Where & how would I put
this into the Access Database? Under Module? I have done this and it gives
me a message statement "undefined function 'PlusOne' error saying in
expression, when I put the query with the properties you have stated. My
co-worker and I have been working on this and still no luck. Any further
assistance you could give us would be appreciated. Thanks

 

[LRay67]

John, thanks for the information.

There is no AutoNumber Field (that would of been great), but there isn't

When you state you have to create a ranking query and use the calulated
value in the statement below - what does TA and TB mean in this statement. I
understand the TA.* (which is everything in the table) but I am confused on
the TA and TB. Please help Thanks

LRay67

 

[LRay67]

John, thanks for the information.

It would of been nice if there was an AutoNumber field, but there isn't

In the statement below on creating a ranking query what is the TA & TB stand
for? I am not an advanced in access as I probably should be. I understand
the TA.* means everything in the table (right?)

Please help further, thanks

LRay67

 

[John Spencer]

The TA and TB point to separate instances of the table. TA is one "copy"
and TB is a second "copy". You don't make a copy, the computer just
uses the table twice.

TA.* means display all the fields in the TA table. You can also list
just the fields you want to display. Usually a good idea if you only
need a some of the fields.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================