Union query with unique values, without sorting in alphabetical order

[Tiana]

I have run a union query, taking the max value from different tables, listing them into the one column.
The results may look something like this:

7


23
2
23
18

I want only to show unique values, though. 23 is listed twice above, so I used the DISTINCT function to show only unique values.
However, when I run the distinct function, access changes the order to alphabetical when I want to maintain the same order as I've written in the union query, only with the duplicates removed. Like this:

7
23
2
18

Is this possible?
It seems I have to do one or the other

[ranman256]

make a new select query that uses the union query,
set UNIQUE VALUES = true, (properties )
and sort.

[Tiana]

Setting the unique values to true is the same as distinct. It returns numerical order. I don't want numerical order, I need it to be the same order as the query originally returned

[ranman256]

Queries only sort on a field.
if you don't have the field,it won't sort.

[June7]

UNION does not allow duplicate records. UNION ALL will. Unfortunately for you, UNION automatically sorts records whereas UNION ALL does not.

I selected UniqueRecords (DISTINCTROW) instead of UniqueValues (DISTINCT) in query design and records from the UNION ALL were not sorted but duplicate records remain.

What you want might not be possible unless you write data to a 'temp' table.

Why do you need this?

[Tiana]

I'm sorry, I'm having trouble understanding what you're trying to say? I've run a 2nd query from the union query, set the properties to distinct, but it's still returning the values in numerical order

[orange]

Perhaps you could provide an example of your data and the query(s) you are using. Also, show an example of the output you expect/need. You may find this w3schools link helpful.

[Tiana]

The SQL is pretty simple, it gets it's data from other queries/tables and summarises it for me. This is an example of my best attempt at getting the results in order:

SELECT First ([Table 1].[Column 1]) AS [Number], 1 AS Order FROM [Table 1]
UNION
SELECT First ([Table 2].[Column 1]), 2 FROM [Table 2]
UNION
SELECT First ([Table 3].[Column 1]), 3 FROM [Table 3]
UNION
SELECT First ([Table 4].[Column 1]), 4 FROM [Table 4]
UNION
SELECT First ([Table 5].[Column 1]), 5 FROM [Table 5]
ORDER BY Order;

The results are now in the same order that I want them to be in (i.e. table 1 goes first, then table 2 etc).
I then run a new query with the following SQL:

SELECT DISTINCT [Query 1].Number
FROM [Query 1];

I have unique values now, but they are in numeric order. If I remove the word DISTINCT, they are in the correct order but have duplicates.
I don't want Access to sort the results into numeric order. Is there a way around this?

[orange]

What exactly does First mean to you in terms of your query?

There is confusion with First and Last as evidenced in:
Well, First() means the first value for that column in the recordset
in its present order, Last() means the last value.

It's no different than the fact that TOP 10 on a query sorted in
descending data order will return a different set of records than
TOP 10 on the same query sorted by CompanyName ascending.

It should be self-evident that the terms First() and Last() are
dependent on the ordering of the domain to which they are applied.
Why this confuses people into saying that they return
non-predictable results, I can't say. It's only non-predictable if
you're ignoring the sort order.

[Tiana]

Yes I'm aware of the first() concept. The table that the query is referencing has already been sorted, so I need to return what's in column A row 1 (the first record). The union query is correct, I've manually checked it and it has given me the correct results.

The results are:
7 (from table 1)
23 (from table 2)
2 (from table 3)
23 (from table 4)
18 (from table 5)

The issue I'm having is with the next step..
how can I remove the 2nd 23 (from table 4). I don't need it in my results, I only need unique values. When I use DISTINCT to get unique values, the correct unique values are returned, however they are now in numeric order, which is not what I want

[orange]

Can you post a copy of your database so we can review/experiment?

[Tiana]

I would, but it contains confidential information. It can be experimented on any union query, really. I'm just looking for a way to keep the results in the same order and remove any duplicates. I appreciate the time you've spent trying, but surely someone has figured it out before. I'll just keep looking for a solution

[orange]

I mocked up a sample similar to your data.

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.

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.

[Cottonshirt]

it seems to me that you are trying to sort them by table no, in which case why not just include table no as a field in the UNION query.

then, to get DISTINCT values you seem to want the value from the lowest numbered table, so use an AGGREGATE query where you GROUP them by value and take MIN(table no).

then sort by table no, value,


good luck with your project,


Cottonshirt