Sort query results according to criteria from different table 
Author Message
 Sort query results according to criteria from different table

Hi NG,

I have a SQL UNION query which returns the customer's ID number (primary key
from the table customer) for each customer who had transactions in the
respective week (table transactions). Now I want these ID numbers to be
sorted according to the customer's name which is stored in a different
table (table customers). Does anyone have an idea how I can order the
numbers by criteria from another table?

Any help, link etc. appreciated

Kind regards

Lukashimmelslaeufer



Sun, 04 Dec 2005 16:54:24 GMT  
 Sort query results according to criteria from different table
Create another query.
Use the union query as an input table, along with your customer table.
Join the on the ID field.
Sort on the customer name.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to the newsgroup. (Email address has spurious "_SpamTrap")


Quote:
> Hi NG,

> I have a SQL UNION query which returns the customer's ID number (primary
key
> from the table customer) for each customer who had transactions in the
> respective week (table transactions). Now I want these ID numbers to be
> sorted according to the customer's name which is stored in a different
> table (table customers). Does anyone have an idea how I can order the
> numbers by criteria from another table?

> Any help, link etc. appreciated

> Kind regards

> Lukashimmelslaeufer



Sun, 04 Dec 2005 17:25:15 GMT  
 Sort query results according to criteria from different table

Quote:

> Create another query.
> Use the union query as an input table, along with your customer table.
> Join the on the ID field.
> Sort on the customer name.

Thanks, Allen!

Unfortunately I am not an expert for SQL queries. The assistant does not
allow to merge a UNION query with other queries and I am not sure how to
use the JOIN command. I have tried something like

SELECT qry1.Customer
FROM qry1 JOIN qry1 ON Customers, Customers.ID
SORT BY Customers.Lastname

I get the message "Error in JOIN operation" but don't know how to fix it.

Could you help again?

TIA

Lukashimmelslaeufer



Sun, 04 Dec 2005 18:24:23 GMT  
 Sort query results according to criteria from different table
The query design window cannot display the UNION query graphically. However,
if you create another query using the union query as in input table, it can
display that graphicallly. You should not need to write the SQL yourself.

If you do have to write it yourself, the FROM clause will be something like
this:
FROM Customers INNER JOIN qry1 ON Customers.ID = qry1.ID

--
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to the newsgroup. (Email address has spurious "_SpamTrap")


Quote:

> > Create another query.
> > Use the union query as an input table, along with your customer table.
> > Join the on the ID field.
> > Sort on the customer name.

> Thanks, Allen!

> Unfortunately I am not an expert for SQL queries. The assistant does not
> allow to merge a UNION query with other queries and I am not sure how to
> use the JOIN command. I have tried something like

> SELECT qry1.Customer
> FROM qry1 JOIN qry1 ON Customers, Customers.ID
> SORT BY Customers.Lastname

> I get the message "Error in JOIN operation" but don't know how to fix it.

> Could you help again?

> TIA

> Lukashimmelslaeufer



Sun, 04 Dec 2005 18:31:49 GMT  
 Sort query results according to criteria from different table

Quote:

> The query design window cannot display the UNION query graphically.
> However, if you create another query using the union query as in input
> table, it can display that graphicallly. You should not need to write the
> SQL yourself.

> If you do have to write it yourself, the FROM clause will be something
> like this:
> FROM Customers INNER JOIN qry1 ON Customers.ID = qry1.ID

Thank you very much. It works fine.
But still no chance to do the job graphically. As soon as I try to switch
from SQL view to the other, Access 2002 crashes. But I can live with such
problems.

Thanks again and best regards

Lukashimmelslaeufer



Sun, 04 Dec 2005 19:50:23 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Different query results in Access 2000 than in VB

2. ??? Result of a Query into different Access 2.0 database

3. LIKE query gives different results in Access 2000 and VB6 SP5

4. HELP...Queries return different results in VB than Access

5. Two identical queries returning different results

6. Can't sort result set on query based on calculated values

7. Need help querying a table with multiple criteria!

8. Native code results different from p-code results

9. sort two different recordsets into one table

10. How to copy query results to linked table

11. Different outlook modules according to profile

12. SQL select different results on different forms

 

 
Powered by phpBB® Forum Software