sort union query

  • Thread starter Thread starter tim johnson
  • Start date Start date
T

tim johnson

I am trying to make a Union query from two querries.
Each query has 2 fields selected for the same table,
tblCustomer. If the customer is a company then I put the
company name in a field called Company.

If customer is an individual I have a FirstName, LastName
and MI field for this individual. I have validation
procedures so that the same record cannot have a company
name and customer name.

Because company and individual are in separate I would
like to join them and display in a combo box


The first query, qryUnionIndividual has these 2 fields:
1. CustomerID
2. Customer
I concatanated the FirstName, LastName and MI of customer
in this query as follows;
Customer: [LastName] & " " & [FirstName] & " " & [MI]

The second query, qryUnionCompany has these 2 fields:
1. CustomerID
2. Customer

The two fields does join to give a column with both names
but I am unable to get the field to sort alphabetically

This is the SQL string to create the union query:

SELECT CustomerID, Customer FROM qryUnionIndividual ORDER
BY Customer UNION SELECT CustomerID, Customer FROM
qryUnionCompany


How can I get this to sort?
 
You have the ORDER BY clause in the wrong place. Try this:

SELECT CustomerID, Customer
FROM qryUnionIndividual
UNION
SELECT CustomerID, Customer FROM
qryUnionCompany
ORDER BY Customer;

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top