Can a Union query work with athe random function (1 Viewer)

[Poco_90]

Hi,
I have 3 queries that I need to join. the 3 queries work on their own. They are all similar to below

SELECT TOP 5 ASTDATA.[ID], ASTDATA.[Weight], ASTDATA.[StockCode], ASTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM ASTDATA
ORDER BY Rnd([ID]);

But when I join them, like below, the data doesn't change. Does the rnd function work with a union query?

SELECT TOP 5 ASTDATA.[ID], ASTDATA.[Weight], ASTDATA.[StockCode], ASTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM ASTDATA
UNION
SELECT TOP 5 BSTDATA.[ID], BSTDATA.[Weight], BSTDATA.[StockCode], BSTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM BSTDATA
UNION SELECT TOP 5 CSTDATA.[ID], CSTDATA.[Weight], CSTDATA.[StockCode], CSTDATA.[CurrentQty], Rnd([ID]) AS Expr1
FROM CSTDATA;


TIA,
Poco

 

[Pat Hartman]

It appears that the randomizer resets after each query and since it is using the same seed will always generate the same numbers in the same sequence. Remove the Rnd() from the union query. Create a new query that selects the union query and add the Rand() there. That will work over the entire set of data.

 

[Poco_90]

Hi Pat,
Thank you for your reply.

When I tried as you suggested it does not make any difference. I removed the Rnd function from the union query as well as the Top 5

SELECT tblASTDATA.[ID], tblASTDATA.[Weight], tblASTDATA.[StockCode], tblASTDATA.[CurrentQty]
FROM tblASTDATA
UNION
SELECT tblBSTDATA.[ID], tblBSTDATA.[Weight], tblBSTDATA.[StockCode], tblBSTDATA.[CurrentQty]
FROM tblBSTDATA
UNION
SELECT tblCSTDATA.[ID], tblCSTDATA.[Weight], tblCSTDATA.[StockCode], tblCSTDATA.[CurrentQty]
FROM tblCSTDATA

Now the union query is pulling in all the data from the 3 tables.

Then I created a new query

SELECT TOP 15 qryQuery2.[ID], qryQuery2.[Weight], qryQuery2.[StockCode], qryQuery2.[CurrentQty], Rnd([ID]) AS Expr1
FROM qryQuery2;

But the data still isn't changing.
Regards,
Poco

 

[Pat Hartman]

Did you use the Randomize() function to seed the randomizer before you ran the query?

 

[Poco_90]

Hi Pat,
I tried a query with thre Rand function on my union query

SELECT TOP 15 Query2.[ID], Query2.[Weight], Query2.[StockCode], Query2.[CurrentQty], Rand([ID]) AS Expr1
FROM Query2;

and I get undefined function Rand in expression. I am using access 2010.

Poco

 

[Pat Hartman]

This is the help entry for the Randomize statement. It is what sets the seed for the Rnd() function.

Randomize Statement
 Collapse image Expand Image Copy image CopyHover image

This page is specific to the Visual Basic for Applications (VBA) Language Reference for Office 2010.

Initializes the random-number generator.

Syntax

Randomize [number]

The optional number argument is a Variant or any valid numeric expression.

Remarks

Randomize uses number to initialize the Rnd function's random-number generator, giving it a new seed value. If you omit number, the value returned by the system timer is used as the new seed value.

If Randomize is not used, the Rnd function (with no arguments) uses the same number as a seed the first time it is called, and thereafter uses the last generated number as a seed value.


NoteNote

To repeat sequences of random numbers, call Rnd with a negative argument immediately before using Randomize with a numeric argument. Using Randomize with the same value for number does not repeat the previous sequence.


Example


This example uses the Randomize statement to initialize the random-number generator. Because the number argument has been omitted, Randomize uses the return value from the Timer function as the new seed value.






Dim MyValue

Randomize ' Initialize random-number generator.



MyValue = Int((6 * Rnd) + 1) ' Generate random value between 1 and 6.

 

[94Sport5sp]

Hi:

I came across this thread as I was trying to do something similar to the OP query and I encountered the same problem as the OP in that the number returned was always the same.

In researching the problem I determined a couple of important item which need to be understood:

1st RND() is not a true random number generator but rather a retrieval system which retrieves based on the seed. So if the seed does not change than the returned number will not change.

2nd The number returned will always be a decimal fraction of zero. So, if the OP was looking for numbers like 1, 5, 11, then that would not happen.

3rd For a query, specifying a non zero number [as in RND(9)] causes the RND function to return the same number. I think this is because the query resets the seed for each record retrieved.

4th For a query, using a variable which evaluates as zero will always return the last number generated by RND.

So, if I am onto the right track, if the [ID] part of Rnd([ID]) in the OP query is evaluating to zero then the last number generated will be returned. (Remember the returned number will always be a decimal fraction of zero.) To test this the OP could change Rnd([ID]) to Rnd([ID] + .00001) [or any decimal fraction] and the result should be a changing random decimal fraction of zero.

Did you use the Randomize() function to seed the randomizer before you ran the query?

Also, Pat, I believe the Randomize function is only available in VBA at least is my A2003 system the query engine does not recognize it but VBA does

 

[Pat Hartman]

No the query engine does not recognize Randomize(). Do it in VBA before you run the query. It still might not work because the query engine and Access are probably not working in the same memory space.

 

[Poco_90]

I,ve had to put this on the back burner. Thanks for getting back to me. I will try again with your suggestions.
Regards,
Poco

 

[Poco_90]

I have to admit defeat on this one. I can't seem to get it to work. I'll try and go at it from a different angle and get the 3 queries to load into a form/table one at a time. Thanks for all your help with this.