• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 560
  • Last Modified:

Query to select random number


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


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"
  • 2
  • 2
1 Solution
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.
Rey Obrero (Capricorn1)Commented:
try this query, no need to use temp table and another query

Select T.*
From Table2 As T
(SELECT TOP 50 PERCENT T2.ID, T2.[Field Value], T2.[Form Value], Rnd([ID]) AS [Random Number Sort]
FROM Table2 As T2
WHERE T.[Form Value] = "Mike" And T3.ID Is Null
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
Rey Obrero (Capricorn1)Commented:
try this run query3
maximyshkaAuthor Commented:
Thanks a lot!!!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now