Selecting random records from MS Access table With Union

Question

I'm trying to collect a random sample of records from an Access table from 3 different groups/buckets. In my example, I want 50 random records from each [Duration of the Call] bucket for a total of 150 records. I join each group with a UNION clause. The results being returned are not random and each bucket of 50 are just being pulled from the first Date of the Call in 1/2/2018. It's like the TOP clause is just looking at the first 50 records in the table which match the criteria but I want truly random sample from the whole table. Thanks

SELECT * FROM ( 
SELECT TOP 50
 [Workgroup],[Last Name],[First Name],[Titanium Number],
[Phone Number], [Inbound-Outbound], [Date of the Call], [Time of the Call], 
[Duration of the Call], ID 
FROM PCA_Calls WHERE 
([Date of the Call] >=  #1/1/2018# ) AND 
([Date of the Call] <=  #1/31/2018# ) AND 
([Duration of the Call] >= 420) AND ([Duration of the Call] <=900) AND 
([Workgroup] =  "PCA0001A" ) 

UNION 
SELECT TOP 50
 [Workgroup],[Last Name],[First Name],[Titanium Number],
[Phone Number], [Inbound-Outbound], [Date of the Call], [Time of the Call], 
[Duration of the Call], ID 
FROM PCA_Calls WHERE 
([Date of the Call] >=  #1/1/2018# ) AND 
([Date of the Call] <=  #1/31/2018# ) AND 
([Duration of the Call] >= 901) AND ([Duration of the Call] <=1800) AND 
([Workgroup] =  "PCA0001A" ) 

UNION 
SELECT TOP 50
 [Workgroup],[Last Name],[First Name],[Titanium Number],
[Phone Number], [Inbound-Outbound], [Date of the Call], [Time of the Call], 
[Duration of the Call], ID 
FROM PCA_Calls WHERE 
([Date of the Call] >=  #1/1/2018# ) AND 
([Date of the Call] <=  #1/31/2018# ) AND 
([Duration of the Call] >= 1801) AND ([Duration of the Call] <=2700) AND 
([Workgroup] =  "PCA0001A" ) 
) AS Sub ORDER BY rnd(INT(NOW*ID)-NOW*ID);enter code here

If something doesn't work, show how you applied it, and what you mean with doesn't work. Do you get an error, unexpected results etc — HoneyBadger, Feb 21 '18 at 13:35
Adding ORDER BY to each of the queries in the UNION produces the error "the ORDER BY expression (Rnd(-1000*ID)*Time())) includes fields that are not selected by the query. Only those fields requested in the first query can be included in the ORDER BY expression". You can have only 1 ORDER BY when using UNION. — evanburen, Feb 21 '18 at 13:48
Just use subqueries to avoid that error. (`SELECT * FROM (SELECT TOP 50 etc FROM something ORDER BY something)`) — Erik A, Feb 21 '18 at 14:12

score 0 · Answer 1 · answered Feb 21 '18 at 13:35

0

Remove the outer random ordering and apply it to each of the queries.

Also, modify the expression to sort on as shown here:

Random sorting query Access

answered Feb 21 '18 at 13:35

Gustav

53,498
7
29
55

This produces the error "produces the error "the ORDER BY expression (Rnd(-1000*ID)*Time())) includes fields that are not selected by the query. Only those fields requested in the first query can be included in the ORDER BY expression" – evanburen Feb 21 '18 at 13:50
Oh, you are right, that is so. You may have to create three (sub)queries each pulling 50 random records, then union these three queries in a normal union query. – Gustav Feb 21 '18 at 14:32

Selecting random records from MS Access table With Union

1 Answers1