maximyshka
asked on
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
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
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot!!!
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.