Can a Union query work with athe random function

[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

 

[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

 

[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

 

[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

 

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