Code Union Query

A

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
 
A

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SELECT DISTINCT query from three sources possible? 11
Help with SELECT UNION 7
SELECT/UNION query not working 13
Union Query 4
Data Mismatch in Excel 2010 4
UNION QUERY.. 3
Query Criteria 2
Union Query 3

Top