Query - Randomly Selecting Records from Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
'====================================================
 
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
 
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
 
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
 
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
'====================================================
 
Back
Top