Help with Query - sorting - is this possible?

[stephenmbell]

I am in the middle of building a small web based contacts application. Prior to this, my client was using Yahoo for their contacts - but certain security issues have come up.

My question is this..

my contacts table looks somewhat like this..
----------
id
firstname
middle
lastname
homephone
workphone
mobile
fax
other
address
companytitle
jobtitle
companyaddress
notes
----------

And here is where I am stuck... I am using an Access DB for a backend. I want to query the contacts and order them by Lastname, Firstname **BUT - if there is a company name, I want to order by that

example:

Adams, Mike
Andrews, Dave
Apple Store
Bell, Dan
Best Western

is this possible with having all of the data in the same table??

i am currently using
select * from contacts order by lastname, firstname, companytitle
Thanks.

 

[rac2]

You could do this with a UNION query.

SELECT lastname & firstname AS ContactName
FROM contacts

UNION

SELECT companytitle
FROM contacts


ORDER BY ContactName

This combines the results of two different queries. You must have the same number and type of columns in both SELECT lists; otherwise the queries can retrieve different columns from the same table or from different tables.

See Access Help topic Create an SQL-specific query for more on creating a UNION query.

 

[traingamer]

Or just

SELECT IIf(Len([CompanyName] & "")=0,[LastName] & ", " & [FirstName],[companyName]) AS DispName
FROM tblContacts
ORDER BY IIf(Len([CompanyName] & "")=0,[LastName] & ", " & [FirstName],[companyName]);

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill