Query to select random number

Hi.


I'm doing multitasks which involve use of top 50 percent based on random number of ID.
I select any records with Form Value "Mike".

Please see 2 databases attached:

1) In database 2.1 I create temp table "temp table-top 25 percent random id" by selecting 50% random numbers of ID from "table2" and using sort of random numbers. This is done in Query 1. In the Query 2, I select a record with Name "Mike" and exclude results from the temp table. The correct output is 1 record
2) In database 2.2 I'm doing same thing by using queries only.  This provide incorrect results of 3 records

Questions:

1. Can I modify Database 2.2 so I can use "queries only" approach and get correct results.
Note: I have to be sure that records which I exclude based on 50% from random number sorts of ID.

Relevant information: main table is "table2"
Name stored in the field "Form Value"
Database-2-1.accdb
Database-2-2.accdb
maximyshkaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

als315Commented:
I don't understand your idea. In saved table you have 3 records for Mike, so in Query2 is only one record. But with random order you will have different records in query1 each time.
Query2 in second DB give you diffrent results each time and it is expected result.
Remove Is Null criteria from query2 and you will see why.
0
Rey Obrero (Capricorn1)Commented:
try this query, no need to use temp table and another query


Select T.*
From Table2 As T
LEFT JOIN
(SELECT TOP 50 PERCENT T2.ID, T2.[Field Value], T2.[Form Value], Rnd([ID]) AS [Random Number Sort]
FROM Table2 As T2
ORDER BY Rnd([ID])) AS T3
ON T.ID=T3.ID
WHERE T.[Form Value] = "Mike" And T3.ID Is Null
0
maximyshkaAuthor Commented:
Hi Capricorn.  Thanks for your answer, however something wrong with the provided query.
When I copy that to the database and run the query, I got first answer as

Table2.ID      Field Value      Form Value      Query1.ID
1      0.5      Mike      

Than I put this query to design mode and run it again.

Records being appended instead of getting another 1 record.

Table2.ID      Field Value      Form Value      Query1.ID
1      0.5      Mike      
4      3      Mike      

Please see query 2 in the attached database.  Just run it, put it in the design mode and run it again.  I attached db for your convenience
Database-2-2.accdb
0
Rey Obrero (Capricorn1)Commented:
try this run query3
Database-2-2rev.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
maximyshkaAuthor Commented:
Thanks a lot!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.