Random 100 Records

[simon4amiee]

Hi all,

I have 3 fields:

CustomerID (Alpanumeric)
AttendDate (General Date)
Area (Alphanumeric)

Is is possible in a query to pull back 100 random CustomerID's on a set AttendDate.

For example I have 1000's of Customers who attend each day, I want to do some Data Quality on 100 Random Customers.

If thats easy, is it then possible to show no more than 5 in any Area.

 

[CJ_London]

you can use the random function (rnd()) for numbers, but I'm not aware of a way of randomising text.

if your example is a table, perhaps you can add an autonumber field to uniquely identify each record and then take a look at this link

http://www.techrepublic.com/blog/ho...-a-random-set-of-records-in-microsoft-access/

 

[nfk]

SELECT TOP 100 t.CustomerID 
FROM t 
WHERE t.AttendDate BETWEEN '29/03/1987' AND '06/06/2015' 
ORDER BY RND(t.CustomerID)

The BETWEEN clause I just wing it, I think is like that on Access.

Other ways for other SQL flavours: http://www.petefreitag.com/item/466.cfm

 

[vbaInet]

Unfortunately that's not how Access works when it comes to generating random numbers nfk. For a true list of random records in Access, the link that CJ_London has in his post utilizes the Randomizer which helps to re-initialise the seed.

If thats easy, is it then possible to show no more than 5 in any Area.

With regards this, you'll need to use a subquery to return the Top say 10 records per area. Save this query, build another query off this and follow what's in the link that CJ_London suggested.

Here's a link on subqueries:
http://allenbrowne.com/subquery-01.html

 

[jdraw]

There was a similar post a few years back with an approach that may work for you.

 

[nfk]

Unfortunately that's not how Access works when it comes to generating random numbers nfk. For a true list of random records in Access, the link that CJ_London has in his post utilizes the Randomizer which helps to re-initialise the seed.

With regards this, you'll need to use a subquery to return the Top say 10 records per area. Save this query, build another query off this and follow what's in the link that CJ_London suggested.

Here's a link on subqueries:
http://allenbrowne.com/subquery-01.html

I didnt get from the op the he was trying to generate random numbers I think he wants to pull a 100 random records from a table, if thats the case the query I posted is working fine for me.

 

[vbaInet]

nfk, you generate random numbers in order to get random results. Have you read the article?

 

[nfk]

nfk, you generate random numbers in order to get random results. Have you read the article?

I read the OP's question and posted a query that does the job, everything else is crumbs for the pidgeons.

 

[spikepl]

@nfk

osted a query that does the job

Not that query. Not in Access. Post SQL that fits Access or dont post, if you do not know the difference.

 

[AccessBlaster]

SELECT TOP 100 t.CustomerID 
FROM t 
WHERE t.AttendDate BETWEEN '29/03/1987' AND '06/06/2015' 
ORDER BY RND(t.CustomerID)

The BETWEEN clause I just wing it, I think is like that on Access.

Other ways for other SQL flavours: http://www.petefreitag.com/item/466.cfm

The reason people abandon this method: When you close and reopen the form many times the same random records would appear. In CJ's example it's virtually impossible for this to happen. Do to the random seed generator based on the autonumber and sorting.