Random Selection of Records (1 Viewer)

thenoisydrum · Oct 23, 2018

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 · Oct 23, 2018

Why random?

thenoisydrum · Oct 23, 2018

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

MajP · Oct 23, 2018

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.

Code:

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 · Oct 23, 2018

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

MajP · Oct 23, 2018

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 · Oct 23, 2018

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.

Code:

    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 · Oct 23, 2018

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 · Oct 24, 2018

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 · Oct 24, 2018

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 · Oct 24, 2018

No it will not. just tested it many times

Yes it most certainly will.

If I create this query.

Code:

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

Code:

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

Code:

King
suyama
buchanan
callahan
dodsworth

JHB · Oct 24, 2018

I think you and arnelgp post in the wrong thread!

jdraw · Oct 24, 2018

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.

Code:

'---------------------------------------------------------------------------------------
' 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 · Oct 24, 2018

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

Code:

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 · Oct 25, 2018

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 · Oct 25, 2018

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 · Oct 25, 2018

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.

Code:

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 · Nov 2, 2018

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

Random Selection of Records (1 Viewer)

thenoisydrum

Registered User.

JHB

Have been here a while

thenoisydrum

Registered User.

MajP

You've got your good things, and you've got mine.

jdraw

Super Moderator

MajP

You've got your good things, and you've got mine.

The_Doc_Man

Immoderate Moderator

AccessBlaster

Registered User.

JHB

Have been here a while

arnelgp

..forever waiting... waiting for jellybean!

MajP

You've got your good things, and you've got mine.

JHB

Have been here a while

jdraw

Super Moderator

isladogs

MVP / VIP

MajP

You've got your good things, and you've got mine.

isladogs

MVP / VIP

AccessBlaster

Registered User.

Attachments

thenoisydrum

Registered User.

Similar threads

Users who are viewing this thread