Generate random table and display top three results.

[TToc2u]

I am failry new to access, but am pretty computer savy.



I am trying to create a query that pulls three random DISTINCT records from a table. I am able to get the top three random records ok, but I cant figure out how to make sure they are Distinct records.

I would like them to be disticnt animals from the "animal column"

This is what I have:

SELECT TOP 3 Enrichment.Animal, Enrichment.Catagory, Enrichment.Item, Enrichment.Comments
FROM Enrichment
GROUP BY Enrichment.Animal, Enrichment.Catagory, Enrichment.Item, Enrichment.Comments, Enrichment.ID, Rnd([ID])
ORDER BY Rnd([ID]);


I will then want to creat a form that has three text boxs. One displays informaiton from row 1, the second dissplays information from row 2 , and the third displays information from row 3.

[June7]

This seems to do something, requires a unique ID field in source table:
SELECT TOP 3 Int(Rnd([ID])*6) AS RandomNum, Enrichment.Animal, Enrichment.Catagory, Enrichment.Item, Enrichment.Comments
FROM (SELECT Max(ID) AS MaxOfID FROM Enrichment GROUP BY Animal) As MaxEnrichmentID
LEFT JOIN Enrichment ON MaxEnrichmentID.MaxOfID = Enrichment.ID
ORDER BY Int(Rnd([ID])*6);

This might be better http://access.mvps.org/access/queries/qry0011.htm

What do you mean by three textboxes, one for each row? Textboxes display fields. Build form that has textbox for each field, set form to Continuous or Datasheet view. The records will display in tabular arrangement.

[TToc2u]

Thanks for helping out. That SQL string works great except that it generates the same 3 or 4 results each time the query is first loaded. I tried to modify the string you have me by multiplying it by 100 instead of 6, but it didnt help. Should I do a NEW ID function?

SELECT TOP 3 Enrichment.Animal, Enrichment.Catagory, Enrichment.Item, Enrichment.Comments, Enrichment.[Rating (1-3)]
FROM (SELECT Max(ID) AS MaxOfID FROM Enrichment GROUP BY Animal) AS MaxEnrichmentID LEFT JOIN Enrichment ON MaxEnrichmentID.MaxOfID = Enrichment.ID
ORDER BY Int(Rnd([ID])*100);

[orange]

See this article, especially the RANDOMIZE statement,

http://www.techrepublic.com/blog/how...oft-access/149

[June7]

Interesting, test with my data seemed to work. That is a great article Orange. A complication of OP's requirement is they don't want multiple records with same animal. My suggestion was attempting to accommodate that. I knew that always pulling the Max ID for each animal was not a totally random process but I am at a loss on how to approach that restriction.

[TToc2u]

I was able to generate a random list of records by using the following:

 
Function Randomizer() As Integer
Static AlreadyDone As Integer
If AlreadyDone = False Then Randomize: AlreadyDone = True
Randomizer = 0
End Function

 
SELECT Top 3 Rnd(IsNull([enrichment].[ID])*0+1) AS Expr1, Enrichment.Animal, Enrichment.Catagory, Enrichment.Item, Enrichment.Comments, Enrichment.[Rating (1-3)]
FROM Enrichment
WHERE (((Randomizer())=0))
GROUP BY Rnd(IsNull([enrichment].[ID])*0+1), Enrichment.Animal, Enrichment.Catagory, Enrichment.Item, Enrichment.Comments, Enrichment.[Rating (1-3)]
ORDER BY Rnd(IsNull([enrichment].[ID])*0+1);

This works really well but I am still unable to specify DISTINCT values of "animals" basically I want it to look like this

From this:

1, a, c,
1, r, t, 
2, h, e,
3, l, d,
Ect...

To This:

1, r, t, 
2, h, e,
3, l, d,