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

[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.

 

[Michel Walsh]

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

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.

 

[Michel Walsh]

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

I tried using it like this
SELECT [List_of_Venders-AFTER_CLEAN_STEP1].ID,

[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);'

 

[Guest]

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

 

[Michel Walsh]

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

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