Random Selection of Records

[thenoisydrum]

Hi,
I have spent a long time trying to find a solution to this but I'm struggling....
I'm not sure whether it is the distinct function that I need but I am hoping that somebody here can help.

My dataset is invoices. Specifically, invoices for Belgian customers. Some of these invoices are for transactions in Belgium (domestic) and some of them are in other countries (non-domestic).

I need to select 2 random invoices from each of the non-domestic countries.
How do I do that?

Originally I was tasked with selecting 5 random non-domestic invoices so used;
SELECT TOP 5 *
FROM [my table]
WHERE [Transaction Country] <> 'BELGIUM'
ORDER BY Rnd([Invoice Number]);

This worked a treat but now that I need to modify it to be 2 random invoices for each of the domestic countries I am stuck.

Can anybody help?

Thanks in advance

 

[JHB]

Why random?

 

[thenoisydrum]

This is for audit purposes so I need to show that I have randomly selected the invoices with no intervention

 

[MajP]

To get the top N per group (you can google more exampes "Top N per Group") you use a subquery. Here is an example. This is top 3 orders per customer ID.

SELECT Orders.CustomerID, Orders.OrderDate, Orders.OrderID
FROM Orders
WHERE Orders.OrderID IN
   (SELECT TOP 3 OrderID                            
   FROM Orders AS Dupe                              
   WHERE Dupe.CustomerID = Orders.CustomerID        
   ORDER BY Dupe.OrderDate DESC, Dupe.OrderID DESC) 
ORDER BY Orders.CustomerID, Orders.OrderDate, Orders.OrderID;

 

[jdraw]

Further to MajP's advice here is a link to Allen Browne subquery tips --see top N records per group

 

[MajP]

Further to MajP's advice here is a link to Allen Browne subquery tips --see top N records per group

That is actually his example.

Also you may want to look up some of my other threads on this subject. Your sql, may or may not give you what you want. If I run your sql 3 times I may return countries

AEGFBC
DQRVS
FWXFN

If I close the database and reopen it and run it three times I will get
AEGFBC
DQRVS
FWXFN

That may or not be what your want. If not you need to build a udf that incorporates "Randomize" prior to running the query.

 

[The_Doc_Man]

Depending on how much time you want to spend on this, I might try this approach:

1. Have a field in the records that you use for ordering. This field COULD be a SINGLE if it would never be used for anything else. Run an update query from VBA to update the field with random numbers. Call it RNDORDER.

    Randomize
    DoCmd.OpenQuery "UPDATE mytable SET RNDORDER = RND() ;'

2. Now use ORDER BY RNDORDER and, using your WHERE clause to filter as needed (for in-country or foreign), take the TOP 2 in the SELECT clause.

 

[AccessBlaster]

See if this link helps. I used a method like this for random pictures, it was very reliable.
https://www.techrepublic.com/blog/h...-a-random-set-of-records-in-microsoft-access/

 

[JHB]

Have you found a solution with queries, else I would suggest some VBA coding?
The challenge here is that you want 2 random invoices for each country, different from Belgium.

 

[arnelgp]

If I close the database and reopen it and run it three times I will get
AEGFBC
DQRVS
FWXFN

No it will not. just tested it many times.

 

[MajP]

No it will not. just tested it many times

Yes it most certainly will.

If I create this query.

SELECT TOP 1 Employees.LastName, Employees.EmployeeID, Rnd([EmployeeID]) AS [Order]
FROM Employees
ORDER BY Rnd([EmployeeID]);

If I open up the database it will return

King
  requery
suyama
  requery
buchanan
 requery
callahan
 requery
dodsworth

If I close my database and open it again I will get

King
suyama
buchanan
callahan
dodsworth

Close and reopen I get

King
suyama
buchanan
callahan
dodsworth

 

[JHB]

I think you and arnelgp post in the wrong thread!

 

[jdraw]

No, they are discussing Randomize -which is required to reseed the Random number.
Without Randomize you will get repeat result.
Here is a function that deals with random.

'---------------------------------------------------------------------------------------
' Procedure : randomNumber
' Author    : Jack
' Created   : 11/18/2010
' Purpose   : To Generate Random numbers between and including a range of numbers.
'Lo and Hi are the lowest and highest random numbers you wish to generate.
'
'The Randomize keyword is critical to getting different results for each Access session.
'=======================================================================================
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Function randomNumber(Lo As Long, Hi As Long) As Long
10       On Error GoTo randomNumber_Error

20    Randomize
30    randomNumber = Int((Hi - Lo + 1) * Rnd + Lo)

40       On Error GoTo 0
randomNumber_Exit:
50       Exit Function

randomNumber_Error:

60        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure randomNumber of Module AccessMonster"
70        GoTo randomNumber_Exit
           
End Function

 

[isladogs]

As jdraw stated, you need to use Randomise to reseed the random number

In an attempt to explain the difference in results for arnelgp & MajP, I tried my own variation of MajP's query on a table with about 1500 records

SELECT TOP 1 PupilData.Surname, PupilData.PupilID, Rnd([PupilID]) AS [Order]
FROM PupilData
ORDER BY Rnd([PupilID]);

I ran it 5 times pressing F5 in between each run. Results were:
PAN
BASTIN
MAGER
HANCOCK
BEDDINGTON

I closed & reopened the db then repeated the tests giving:
MACKENZIE
KIRBY
WALKER
HARDING
DAVIES
In other words, a different list BUT....rinse & repeat several times.
The same list ALWAYS occurs starting with MACKENZIE providing you close the database between each run.
Compacting the database has no effect on the above sequence

However if you close the query but not the database and rerun the query you will get different results each time.

Hope that helps clarify the situation rather than add another layer of confusion!

 

[MajP]

In an attempt to bridge the difference in opinion between arnelgp & MajP

There is really no difference in opinion, what I said is a simple fact. I did not make this up, MS provided this functionality. From the MS site

For any given initial seed, the same number sequence is generated because each successive call to the Rnd function uses the previous number as a seed for the next number in the sequence.
Before calling Rnd, use the Randomize statement without an argument to initialize the random-number generator with a seed.

The behaviour is exactly as I have described. Each number is pseudo random, but each sequence with the same seed is the same sequence.

As I have previously stated this is exactly what you want to happen. For any kind of analysis you want random numbers in repeatable sequences.

 

[isladogs]

OK MajP, let me put it another way.
If you read my post again, I was explaining how both you and arnelgp are correct depending on how the test is run.

But to repeat, use Randomise first to ensure the sequence is always random.

 

[AccessBlaster]

thenoisydrum,

Have a look at this example, you can adjust the query to the output what you need.

Try (F5) thru the pictures or run the query in design view.

The query uses the employee ID as the seed.


HTH
Good luck with your project.

SELECT RandNum([employeeid]) AS Shuffle, Employees.EmployeeID, Employees.LastName, Employees.FirstName, Employees.Title, Employees.TitleOfCourtesy, Employees.BirthDate, Employees.HireDate, Employees.Address, Employees.City, Employees.Region, Employees.PostalCode, Employees.Country, Employees.HomePhone, Employees.Extension, Employees.Photo, Employees.Notes, Employees.ReportsTo
FROM Employees
ORDER BY RandNum([employeeid]);

 

[thenoisydrum]

Thanks to everybody for their help with this one. I had to abandon the project in the end and have only just been able to come back and reply.

All the best

'Drum