I mocked up a sample similar to your data.
Code:
SELECT First ([Table1].[mynum]) as [number] , 1 AS ORDER FROM [Table1]
UNION
SELECT First ([Table2].[mynum]), 2 FROM [Table2]
UNION
SELECT First ([Table3].[mynum]), 3 FROM [Table3]
UNION
SELECT First ([Table4].[mynum]), 4 FROM [Table4]
UNION
SELECT First ([Table5].[mynum]), 5 FROM [Table5]
order by ORDER ;
It errors on the use of ORDER. I had to enclose it in square brackets [ORDER]. Then to get unique values of [Number] the result is returned in numeric sequence.
Was unable to get a different sequence.
I could get a different sequence by using Order By Rnd(number), but that isn't your original order.
Code:
select * from
(select distinct [number] from
(SELECT First ([Table1].[mynum]) as [number] , 1 AS [ORDER] FROM [Table1]
UNION
SELECT First ([Table2].[mynum]), 2 FROM [Table2]
UNION
SELECT First ([Table3].[mynum]), 3 FROM [Table3]
UNION
SELECT First ([Table4].[mynum]), 4 FROM [Table4]
UNION
SELECT First ([Table5].[mynum]), 5 FROM [Table5]
order by [ORDER]) )
order by Rnd([number]);
What is the significance of the original Order in the application? Perhaps you can use vba or ?? to get the sequence you need???
But more detail of the requirement might shed some light on an approach.