Merging the results of two queries

[Goldar]

I have a table (Approved Item Numbers) that contains a field [ITEM KEY] and [Description]. Part of the table contains records where the ITEM NUMBER is constructed like yyyy-q-nnn (y=Fiscal year, q=fiscal quarter and nnn-sequential number). The other part of the table contains records with alphanumeric data. I would like to create a third table (for a combo box) where the first part of the table contains the data from my first table sequenced in decending sequence. The second part of the third table should contain the data from my second table in ascending alphabetic sequence. I have tried to UNION the two tables but no matter what I do, the first part of the output table matches my first table (in ascending sequence) while the second part of the table matches my second table in aphanumeric sequence.

I have tried pre-sorting the first and second tables, but the result is still the same.

Table 1 is created with the query:
SELECT [Item Number],[Description] FROM [Approved Item Numbers] WHERE IsNumeric([ITEM NUMBER],4]=true ORDER BY [ITEM NUMBER] DESC
Table 2 is created with the query:
SELECT [Item Number],[Description] FROM [Approved Item Numbers]
WHERE LEFT(([ITEM NUMBER],1)>='A' ORDER BY [ITEM NUMBER] ASC

What am I doing wrong?

Thanks...

 

[Galaxiom]

Derive another field in the query that will provide the gross order you require when sorted. For example 1 for records from the first set to be displayed and 2 for the second.

Keep the results from the two sources in separate fields with either Null or some appropriate placeholder character in the other field. Nulls sort to the top so you might need to use ZZZZZ or something similar as a placeholder in one to stop them ending up in the wrong place.

Then add another field where the two sets of results are combined. This is the field that will ultimately be displayed and will have a value in every record.

SORT BY DerivedGrossField, [Item Number] DESC, Alphafield

Also don't create more tables. Use a query directly from the original tables.

"Presorting" doesn't work. The only way you can ever rely on the order of records is by sorting in the current query.

 

[Goldar]

Would you please post an example of the technique that you are suggesting? I tried to construct my solution as you said, but ended up with group 1 partially in decending order and group 2 partially in ascending order.

Thanks,