Select Random Records from Unrelated Tables

[DocEE13]

I am fairly new to Access 2013 but am trying to create a query that will select random records from three totally unrelated tables and display the results together as if one table -- think video slot machine wheels. Each table has two fields - ID which is the primary key and NAME. The data in the tables are names of states, names of colors, and types of animals. Each table has a different number of records. My end result is a table that selects X number of random records from each table and displays them side by side like this:

Desired Result:
Animal Color State
cat red Ohio
dog blue Texas
fox green Iowa

I have been able to create three individual queries that will pull X number of random records by using:

SELECT TOP 10 Animals.[ID], Animals.[Name] FROM Animals ORDER BY Rnd(-(100000*[ID])*Time());
SELECT TOP 10 Colors.[ID], Colors.[Name] FROM Colors ORDER BY Rnd(-(100000*[ID])*Time());
SELECT TOP 10 States.[ID], States.[LongName] FROM States ORDER BY Rnd(-(100000*[ID])*Time());

Using the three queries above I get three separate lists.

I don't know how to make one query that will randomly pull from all three tables and make the display above?

 

[bob fitz]

Until someone who actually knows what they are talking about comes along you could try this:
Create and save 4 queries:

qryAnimals:
SELECT TOP 1 Animals.[Name] FROM Animals ORDER BY Rnd(-(100000*[ID])*Time());
qryColors:
SELECT TOP 1 Colors.[Name] FROM Colors ORDER BY Rnd(-(100000*[ID])*Time());
qryStates:
SELECT TOP 1 States.[LongName] FROM States ORDER BY Rnd(-(100000*[ID])*Time());

qryResult:
SELECT DLookUp("Name","qryAnimals") AS Animal, DLookUp("Name","qryColors") AS Color, DLookUp("Name","qryStates") AS State

I haven't tested this.
I believe a better way to resolve this would be to use the first three queries as sub queries in the 4th query but sub queries are still something of a "Dark Art" to me. Tried, but couldn't get it to work. HTH

EDIT
FYI "Name" is a reserved word and should not be used as the name of a field. This may cause a problem. It is better to give fields meaningful names and ID fields like "AnimalID", "AnimalName"

 

[vbaInet]

At the moment what you're doing is:

1. getting the top 10 records from the table
2. sorting in a random fashion.

But what you want to do is:

1. create a query with a field of random numbers, i.e. your Rnd() function will be a field in the query
2. get top 10 records from query in step 1.

 

[vbaInet]

Just going to add that your Rnd() function can be better written. You have two options:

No seed required:
1. Use this

Int(Rnd([ID]*Time())*1000000)

Seed required:
1. Set seed by calling a function that calls Randomize with no parameters in the query. Each query will need seeding or at least one of them should set the seed.
2. And use this

Int(Rnd([ID])*1000000)

My preference would be option 2 because you're not constantly calling the Time() function.

 

[jdraw]

See this article for details and example.
good luck

 

[vbaInet]

Good point jdraw. I forgot about the issues with -ve and zeros.

 

[DocEE13]

I want to thank everyone for their suggestions (Bob Fitz - I changed my field name based on your suggestion - thanks should have know it was reserved). I'm just a beginner so this is all very enlightening even if some of it is still over my head. Sorta teaching myself as I go using what I find on the Internet as a tutorial. It's not systematic but I'm getting there.

jdraw - thanks for the article link. I had found that in my searches and that's how I came up with the original Rnd statement I was using.

vbaInet - I think what you were trying to have me do is what someone on another Forum sent me (see SQL below). This seems to work like I wanted except every once in a while I get a duplicate record from one of the tables. Not sure how to prevent that yet - any suggestions? Also not exactly sure what "Len" is but I've played around with this statement removing Len and DESC to see what happens and the results are not what I want when I change those parts of the statement.

SELECT TOP 3 Animals.AnimalName AS Animals, Colors.ColorName AS Colors, States.StateName AS States
FROM Animals, Colors, States
ORDER BY Rnd(Len(Animals![AnimalID])) DESC , Rnd(Len(Colors![ColorID])) DESC , Rnd(Len(States![StateID])) DESC;

 

[bob fitz]

Info on Len() function: http://www.techonthenet.com/access/functions/string/len.php

 

[vbaInet]

SELECT TOP 3 Animals.AnimalName AS Animals, Colors.ColorName AS Colors, States.StateName AS States
FROM Animals, Colors, States
ORDER BY Rnd(Len(Animals![AnimalID])) DESC , Rnd(Len(Colors![ColorID])) DESC , Rnd(Len(States![StateID])) DESC;

You're still repeating what you already have so I'll re-iterate:

But what you want to do is:

1. create a query with a field of random numbers, i.e. your Rnd() function will be a field in the query
2. get top 10 records from query in step 1.

See this article for details and example.
good luck

Do it exactly as it's explained in the tutorial. She created a randomised field, not just put it in the Order By clause.

1. Create the randomised field
2. Save the query and build another one based on step 1
3. SELECT TOP X from the new query

As for the duplicates, find and remove the duplicates before you attempt to even round. Perhaps just use the DISTINCT keyword.

 

[jdraw]

Further to vbaInet's suggestion, here is a tutorial (slightly different than your direct need [it deals with groups]) but showing how to use Randomize and adding a randomized field.

 

[vbaInet]

Further to vbaInet's suggestion, here is a tutorial (slightly different than your direct need [it deals with groups]) but showing how to use Randomize and adding a randomized field.

What I meant by a random field is a calculated random field in the query just like your first tutorial.

 

[jdraw]

@vbaInet
Understood.
I thought I'd show a sample of Randomize and use of Rnd() with concrete example. I knew the sample existed and hoped it might clarify things for the OP.

 

[vbaInet]

@jdraw, I see. The author in your first link actually uses Randomize and Rnd() in 'Listing A' but because the article is a bit long-winded it's hard to spot.

 

[DocEE13]

bob fitz: Thanks for the link to the Len function it is very helpful for more than just this question.

 

[DocEE13]

You're still repeating what you already have so I'll re-iterate:

Do it exactly as it's explained in the tutorial. She created a randomised field, not just put it in the Order By clause.

1. Create the randomised field
2. Save the query and build another one based on step 1
3. SELECT TOP X from the new query

As for the duplicates, find and remove the duplicates before you attempt to even round. Perhaps just use the DISTINCT keyword.

I think I understand what you are saying about getting the top records and then sorting them in random order vs. sorting the table in random order and then picking the top records from that sort. I'm still trying to figure out the code to do that but what you are saying makes sense. Thanks for the tip.

 

[DocEE13]

Further to vbaInet's suggestion, [Link] (slightly different than your direct need [it deals with groups]) but showing how to use Randomize and adding a randomized field.

Jdraw -thanks for this example. I only have one problem with it ... The nice code that does the randomizing is not something I know how to do. Is that vbscript? I think I understand the concept and will try doing it using Access SQL and design view which I am getting to know.

 

[jdraw]

The code is vba (Visual Basic for Applications). I used the sub procedure PayBandStuff
to populate the field xtrafield in table PayBand with a random number.
Then used SQL to do specific selections.

 

[vbaInet]

I think I understand what you are saying about getting the top records and then sorting them in random order vs. sorting the table in random order and then picking the top records from that sort. I'm still trying to figure out the code to do that but what you are saying makes sense. Thanks for the tip.

No that's not what I'm saying. I've actually explained this twice in bullet points (i.e. steps 1 to N) to make it clear, not sure how else to explain it.

 

[DocEE13]

I think I understand what you are saying about getting the top records and then sorting them in random order vs. sorting the table in random order and then picking the top records from that sort. I'm still trying to figure out the code to do that but what you are saying makes sense. Thanks for the tip.

No that's not what I'm saying. I've actually explained this twice in bullet points (i.e. steps 1 to N) to make it clear, not sure how else to explain it.

vbaInet -- How about sharing the code to do what you are saying?

I thought I understood the difference between what I was doing and what you say I should do and said that in my post, except you seem to have stopped reading before the VS. Please explain with code so I can compare it to mine and see where I need to adjust.

I followed an article found on a UK site called databasedev (sorry wont let me post link). This code does what I want (returns X number of random records).

SELECT TOP 20 Animals.AnimalID, Rnd([AnimalID]) AS Expr1, Animals.AnimalName
FROM Animals
ORDER BY Rnd([AnimalID]) DESC;

However, when I add the other unrelated tables to the code, I start seeing duplicate records.

SELECT TOP 20 Animals.AnimalID, Rnd([AnimalID]) AS Expr1, Animals.AnimalName, Colors.ColorID, Rnd([ColorID]) AS Expr2, Colors.ColorName
FROM Animals, Colors
ORDER BY Rnd([AnimalID]) DESC, Rnd([ColorID]) DESC;

Notice lines 2 and 3 in the results below. Both have AnimalID = 20 and AnimalName = dog but the Expr1 random number is different. I don't understand why this is happening? Unless this is has something to do with what vbaInet has been trying to explain?

RandomAnimal Using ABS
AnimalID Expr1 AnimalName ColorID Expr2 ColorName
15 0.796834707260132 cheetah 39 0.594096124172211 mauve
20 0.956401228904724 dog 6 0.794717848300934 black
20 0.941572189331055 dog 61 0.203096568584442 turquoise
23 0.24976122379303 duck 19 0.803553879261017 ebony

I have also tried the code below and tried replacing LEN with ABS. Still get duplicates once I add the other tables.

SELECT TOP 20 Animals.AnimalID AS AID, Animals.AnimalName AS Animals, Colors.ColorID AS CID, Colors.ColorName AS Colors, States.StateID AS SID, States.StateName AS States
FROM Animals, Colors, States
ORDER BY Rnd(Len(Animals![AnimalID])) DESC , Rnd(Len(Colors![ColorID])) DESC , Rnd(Len(States![StateID])) DESC;

Appreciate anyone's help - just know I am a novice with all this and learning as I go.

Thanks DocEE13

 

[vbaInet]

What do you mean by VS?

To be honest there's no need for code, you already know what you're doing but you're jumping the gun. The idea is that you create the randomised field for the entire dataset, save the query, create another query, then SELECT TOP from the new query. The randomising should be done on the entire table first. This is what step 2 is about which you haven't done.

1. Create the randomised field
2. Save the query and build another one based on step 1
3. SELECT TOP X from the new query

As for the duplicates, find and remove the duplicates before you attempt to even round. Perhaps just use the DISTINCT keyword.

Start with step 1 and 2 and show us what you have.