Randomly returning a percentage of records in a table. (1 Viewer)

Good Afternoon,

I have a database for asset tracking. We are required to do a 10% random inventory each month so that by the end of the 10th month we have inventoried 100% of all assets. After the 10% inventory is completed the date is entered into the table.

I've built a query that will give me the random 10% I'm looking for on the initial run. Each time I run the query I still want it to give me 10% of the TOTAL NUMBER OF RECORDS in the table but only return those that the 10% inventory date is NULL.

My problem is, with the way my code is written it's not pulling from ALL records, only this that don't have a date entered. Here's my code:

SELECT TOP 10 PERCENT [Control Log].*
FROM [Control Log]
WHERE ((([Control Log].[Last 10% Inventory]) IS NULL))
ORDER BY Rnd([ID])

The ID field is the record unique autonumber field that I use as my seed field for the RND function.

Again, all I want the query to do is ALWAYS return 10% of the TOTAL records in the table, not just the records that the 10% date field is empty. Any help is greatly appreciated.

Hi,

I suspect the null values are ignored, and then 10% of the remaining records are being evaluated.

One simple option would be to split this into two queries. The first query would select 10% of the records. The second query would remove the null values.

The following is untested:

Query1:
Query2:

test the data a few times as Random can return the exact same result each time, if you are not careful.

Thanks for the info.

Is it possible to combine into 1 query that would give me the same results or do I have to split the query? I'm trying to make this as simple as possible for the users.

Thanks again.

I'm trying to make this as simple as possible for the users.

Click to expand...

Users should absolutely not have access to tables or queries, or be made to know whether one query generated their data or 10,037 gnomes or pixies slaving away.

You can use your Query2 as RecordSource for a Form, for which you can set the Default View as Datasheet, so it appears the same as a plain table/query, but offers all the possibilities to control what the users can or cannot do with the data.

Further to PNGBill's advice on the random number generator it is worth taking a look at this help page from Microsoft:

http://office.microsoft.com/en-us/access-help/rnd-function-HA001228901.aspxhttp://<a href="http://office.micro...help/rnd-function-HA001228901.aspx</font></a>

If the random number generator is always given the same seed, I think you may indeed get the same sequence of numbers.