can you combine 2 fields to sort on together?

[DM]

For example.
We have Last Name and First Name fields.
We also have a Business Name field.

If we have these names

Smith, Jane
Barr, Paul

and these business names

AAA Plumbing
Trust Co.

We would like the output to be

AAA Plumbing
Barr, Paul
Smith, Jane
Trust Co.

Not, just sorting on one field and then another. Is this possible and if
so, how?

We are using Access 2000.
Thanks for any assistance.

 

[Tom Wickerath]

Hi DM,

It sounds like you need a union query. The following example works in the sample Northwind
database, using the Customers table:

SELECT ContactName AS Contact
FROM Customers
UNION
SELECT CompanyName AS Contact
FROM Customers
ORDER BY Contact;

However, this table includes ContactName in one field (bad design, by the way). If the
ContactName had been stored as ContactFirstName and ContactLastName, then the SQL statement would
read something like this:

SELECT ContactLastName & ", " & ContactFirstName AS Contact
FROM Customers
UNION
SELECT CompanyName AS Contact
FROM Customers
ORDER BY Contact;


Tom
________________________________


For example.
We have Last Name and First Name fields.
We also have a Business Name field.

If we have these names

Smith, Jane
Barr, Paul

and these business names

AAA Plumbing
Trust Co.

We would like the output to be

AAA Plumbing
Barr, Paul
Smith, Jane
Trust Co.

Not, just sorting on one field and then another. Is this possible and if
so, how?

We are using Access 2000.
Thanks for any assistance.

 

[Sylvain Lafontaine]

If the Last Name and First Name fields are null when the Business Name field
is not and vice versa, then you can also use a Case .... End or the Coalesce
function to combine them in one single field.

S. L.