Select Random Records from Unrelated Tables

[DocEE13]

VS means verses as in -- this verses that or this vs. that.

I will see what code I come up with trying what you suggest.

 

[DocEE13]

Okay vbaInet here is the code for what you have been suggesting (I think)...I created three separate queries to randomize all records in each table.

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

qryRandColorsStep1
SELECT Colors.ColorID, Colors.ColorName, Rnd([ColorID]) AS Expr1
FROM Colors
ORDER BY Rnd([ColorID]) DESC;

qryRandStatesStep1
SELECT States.StateID, States.StateName, Rnd([StateID]) AS Expr1
FROM States
ORDER BY Rnd([StateID]) DESC;


Then I created a new query selecting the top 20 records from the Step 1 query.

qryRandAnimalsStep2
SELECT TOP 20 qryRandAnimalsStep1.AnimalID, qryRandAnimalsStep1.AnimalName
FROM qryRandAnimalsStep1;

Results from qryRandAnimalStep2
AnimalID AnimalName
40 iguana
57 ox
70 rat
31 gopher
67 porpoise

So far everything works great! Then I added the other tables to the query and made this code...

qryRandComboStep2
SELECT TOP 20 qryRandAnimalsStep1.AnimalID, qryRandAnimalsStep1.AnimalName, qryRandColorsStep1.ColorID, qryRandColorsStep1.ColorName, qryRandStatesStep1.StateID, qryRandStatesStep1.StateName
FROM qryRandAnimalsStep1, qryRandColorsStep1, qryRandStatesStep1;

Unfortunately I did not get the results I was looking for as you can see below...

Results for qryRandComboStep2
AnimalID AnimalName ColorID ColorName StateID StateName
1 aardvark 1 amber 1 Alabama
1 aardvark 1 amber 2 Alaska
1 aardvark 1 amber 3 Arizona
1 aardvark 1 amber 4 Arkansas
1 aardvark 1 amber 5 California

So what went wrong?

 

[vbaInet]

Ok you're on the right path but you're missing the Randomizer. This one was explained in the first link that jdraw posted.

If I get a minute I'll do a quick sample db and upload.

 

[vbaInet]

See attached qryRandomRecords and qryRandomRecords10