Generate a Table of random 6 Venders from a table of Venders

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am working on a vender database. After selecting a group of venders from a table, I need to select 6 venders from that list RANDOMALLY to produce a report. Thanks in advance.
 
Hi,


Try using:

SELECT TOP 6 *
FROM myTable
ORDER BY RND( numericalFieldHere )


Any expression that does NOT depend of a field is considered a CONSTANT
and so, is evaluated ONCE at the beginning of the process. Using

SELECT RND( ) FROM myTable

would have produce ONE constant, the same, for all records.

Since it is irrelevant to get the random number we generated, we don't need
it in the SELECT clause... it is enough to mention it in the ORDER BY
clause.



Hoping it may help,
Vanderghast, Access MVP


Mitchel Volk said:
I am working on a vender database. After selecting a group of venders
Click to expand...
from a table, I need to select 6 venders from that list RANDOMALLY to
produce a report. Thanks in advance.
 
Hi,


You have two main SELECT clauses, Jet allows just one.

If you meant about

... WHERE fieldName IN( SELECT ... )

you missed the IN, and the parenthesis...



Hoping it may help,
Vanderghast, Access MVP




Mitchel Volk said:
I tried using it like this
SELECT [List_of_Venders-AFTER_CLEAN_STEP1].ID,
Click to expand...
[List_of_Venders-AFTER_CLEAN_STEP1].VENDOR,
[List_of_Venders-AFTER_CLEAN_STEP1].[TYPE OF WORK],
[List_of_Venders-AFTER_CLEAN_STEP1].[WORK CODE],
[List_of_Venders-AFTER_CLEAN_STEP1].[CV #],
[List_of_Venders-AFTER_CLEAN_STEP1].federal_tax_id,
[List_of_Venders-AFTER_CLEAN_STEP1].Vender_list_address,
[List_of_Venders-AFTER_CLEAN_STEP1].Vender_list_city,
[List_of_Venders-AFTER_CLEAN_STEP1].Vender_list_state,
[List_of_Venders-AFTER_CLEAN_STEP1].ZIP,
[List_of_Venders-AFTER_CLEAN_STEP1].PHONE,
[List_of_Venders-AFTER_CLEAN_STEP1].FAX,
[List_of_Venders-AFTER_CLEAN_STEP1].contact_person,
[List_of_Venders-AFTER_CLEAN_STEP1].comments
FROM [List_of_Venders-AFTER_CLEAN_STEP1]
WHERE ((([List_of_Venders-AFTER_CLEAN_STEP1].[WORK CODE])=[Forms]![work code]![Combo16]))
SELECT TOP 6*
FROM List_of_Venders-AFTER_CLEAN_STEP1
ORDER BY RND (ID);

An get this error message Syntex error in query expression
'((([List_of_Venders-AFTER_CLEAN_STEP1].[WORK CODE])=[Forms]![work code]![Combo16]))
SELECT TOP 6*
FROM List_of_Venders-AFTER_CLEAN_STEP1
ORDER BY RND (ID);'
Click to expand...
 
Sorry but that did not work I know that the part before SELCST TOP 6* worked because I tested it before. PLease help me and Thanks in Advance
 
Hi,


Indeed, you can't add a second select.


SELECT *
FROM table1


works,


SELECT *
FROM table2

works, but


SELECT *
FROM table1
SELECT *
FROM table2


does not work. That your first statement work, I have no doubt about it, the
problem is that you add a second statement to it, and even if the second
statement would ALONE work fine too, it is the COMBINATION of the two
statements that makes the error, in JET. With Jet, you must "run" the first
statement, alone, THEN the second statement, itself, alone... but not both
"in the same batch".




Hoping it may help,
Vanderghast, Access MVP




Mitchel Volk said:
Sorry but that did not work I know that the part before SELCST TOP 6*
Click to expand...
worked because I tested it before. PLease help me and Thanks in Advance
 
Back
Top