Code Union Query

[Amateur]

I have a Union Query looking like this:

SELECT ALL * FROM[invoicetrading];
UNION ALL SELECT * FROM[invoicesnontrading];

I would like that the query is sorted by accountnumbers. I.e. I have
accountnumbers like 43001000-6000 - and I would like that the query is
sorting them on the last four digits (Something like: Sort *####). How can I
alter my code so that it is done automatically?

Thanks
Klaus

 

[Allen Browne]

Can we assume that both your tables have the same number of fields, of the
same type, in the same order, and that one of those fields is called
AccountNumber?

Try something like this:
SELECT ALL * FROM [invoicetrading]
UNION ALL
SELECT * FROM[invoicesnontrading]
ORDER BY Right([AccountNumber], 4);

If AccountNumber is actually a Number field (not a Text field), it might be
better to use:
ORDER BY ([AccountNumber] Mod 1000);

Either way, performance will be poor if you have a large number of records.