Record Specific Union Query 1

[Vittles]

I am not sure if this is possible, but I want to make a query for a combo box that uses the 'Institution' values from 2 different table's fields and only have the union query results show up if the ID matches the forms ID. I have this for non-union queries, but not sure on the SQL for this one. Here is my best quess so far - it doesn't work. The 2 tables are joined in a one (CTU Info) to many (CRS Info) - not sure if I needed to include that aspect and how...?

SELECT [CTU Info].[CTU Inst Name] as [Institution], [CTU Info].[CTU ID] FROM [CTU Info]
Where (([CTU Info].[CTU ID])=[Forms]![Main Admin Book Form]![CTU ID])

Union

[CRS Info].[CRS Institution], [CRS Info].[CTU ID] From [CRS Info]
WHERE (([CRS Info].[CTU ID])=[Forms]![Main Admin Book Form]![CTU ID]))
Order by [Institution];

Any ideas?

 

[earthandfire]

I'm assuming the two queries work individually.

UNION SELECT

From memory I think ORDER BY is ignored in UNION queries - I'm sure someone will come in and say if I'm wrong there.

Hope this helps.

 

[PHV]

it doesn't work
Any error message ? Unexpected result ?
Please, elaborate.

Anyway the second where clause has a syntax error and you omit the select instruction.
For me, the following should work:
SELECT [CTU Inst Name] AS Institution, [CTU ID] FROM [CTU Info]
WHERE [CTU ID]=[Forms]![Main Admin Book Form]![CTU ID]
UNION SELECT [CRS Institution], [CTU ID] FROM [CRS Info]
WHERE [CTU ID]=[Forms]![Main Admin Book Form]![CTU ID]
ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[earthandfire]

PHV ORDER BY 1 - thats probably why I've never had much success with ORDER BY in UNION queries.

Out of interest would ORDER BY 2 sort on the second field?

 

[PHV]

would ORDER BY 2 sort on the second field?
Yes.
You may use this syntax:
ORDER BY 2 DESC, 1 ASC

 

[earthandfire]

Thanks. What I've normally done is sort the result of the query - this will make life a lot easier. I think a star is called for.

 

[Vittles]

Thank you - it did work.

I did get a message for missing select....was a late night at work, so my brain must have been partially missing as well.

As to the order question, you can order by 1, 2, etc. or by the field name from what I can see.