UNION Query

[Tom]

I have 2 tables with the following fields:

Table1:
======
[ID]; data type = autonumber
[LASTNAME]; data type = text
[FIRSTNAME]; data type = text

Records will be continously added to this table, so the output could look
like this:

1 Davis John
2 Francis Cindy
3 Jones Mike
4 Stewart Tom
5 Henderson Jackie

Note: The 1st four records were created initially. Later on, "Henderson,
Jackie" joined the organization... creating a query and sorting (ASC) by ID
number would (naturally) put them in the same order as they currently are...
THIS WILL BECOME IMPORTANT IN A MOMENT. Hence, I need to sort on
[LASTNAME].

Now, Table2 schema is the same; however, it's record set is static:
======
[ID]; data type = autonumber
[LASTNAME]; data type = text
[FIRSTNAME]; data type = text

Also (and this may appear strange), I'm not really using names in this list.
This table contains numbers in order to preserve guest member's anonymity.
Due to the last fact, I also leave the FirstName field blank.

Its output (again STATIC) might look like this



Now, I want to combine the records of both tables into a single (UNION)
query. For instance, I might show records of Table1 first. My SQL might
look like this:

==================================
SELECT [Table1].LastName, [Table1].FirstName
FROM Table1 ORDER BY [Table1].LastName

UNION SELECT [Table2].LastName, [Table2].FirstName
FROM Table2;
==================================

The sorted output is okay for records of Table1; however, it is out of sync
for Table2 records (due to data type = text )

1 Davis John
2 Francis Cindy
5 Henderson Jackie
3 Jones Mike
4 Stewart Tom
1 1 ---
10 10 ---
11 11 ---
2 2 ---
3 3 ---
4 4 ---
5 5 ---
6 6 ---
7 7 ---
8 8 ---
9 9 ---


Here's my goal... come up w/ some idea that will allow me to UNION the "real
names" with "numeric names" (e.g. 1 to 11) in such fashion that both
"groups" are sorted in ASC order as shown below:

1 Davis John
2 Francis Cindy
5 Henderson Jackie
3 Jones Mike
4 Stewart Tom
1 1 ---
2 2 ---
3 3 ---
4 4 ---
5 5 ---
6 6 ---
7 7 ---
8 8 ---
9 9 ---
10 10 ---
11 11 ---


The data type of [Table2].[LastName] can be changed to NUMBER...

Any ideas?

Thanks,
Tom

 

[Tom]

Never mind... got it...

Tom