SELECT x AS MyField FROM Table1<BR> UNION<BR>SELECT y AS MyField FROM Table2<BR>ORDER BY MyField<BR><BR>From memory. From memory you specify the field names in the FIRST select, and then add your ORDER BY clause AFTER the final UNION.<BR><BR>However, it's all in Books Online if you want more information.<BR><BR>Craig.
Hmm.<BR><BR>SQL Books Online can be found by clicking on the "SQL Books Online" link on the left (look down, under VBScript Reference). But they'll relate to SQL Server 2000 (probably).<BR><BR>The SQL will be slightly different, but not a lot. Not in this respect anyway. It's a good resource to have.<BR><BR>The only alternative is "Access Help", but that's pants. You need a good SQL book or SQL course.<BR><BR>Craig.
Craig, I've just tried it and the Access just allow you to order it using their field name only which specified after union. but it does not allow me to just insert one 'where' statement to specify the condition which means for both select statement i have to include the 'where' statement. is there any other way to have only one 'where' statement for both table?
I have this one condition top select from both the databases. Currently I'm doing like this<BR>Select Name from Table1 where status = 'Active'<BR>Union<BR>Select Name from Table2 where status = 'Active'<BR>Order By Name<BR><BR>Is it possible to have just one 'where' statement for both select statements?
No, of course it's not.<BR><BR>Do you understand what a UNION does? It combines the results of the two DISTINCT sql queries. You can't share information across them (to my knowledge).<BR><BR>I suggest you go download Books Online and read up.<BR><BR>Craig.