By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,807 Members | 1,492 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,807 IT Pros & Developers. It's quick & easy.

Order By twice?

P: 1
I am creating a database to store a wedding invite list. I am storing fname, lname, phone, street, city, state, zip.

Now i want to create a query that will return all the people in the list, i want to order it by people whos addresses are not in the database first in alphabetical order by last name, and then everyone who is in the the database with an address by alphabetical order by last name. Heres what i have so far:

SELECT fname, lname, phone, street
FROM tblGrooms
ORDER BY street, lname;

Now this will return exactly what i want, but it sorts the second half (the list of names that DO have addresses) by address NOT last name. How can i fix this?

Im sure this is a really simple fix and im just over looking it! Please help!
May 11 '07 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
This question has been posted in the Articles section and is being moved to the Access forum.

ADMIN
May 11 '07 #2

ADezii
Expert 5K+
P: 8,636
I am creating a database to store a wedding invite list. I am storing fname, lname, phone, street, city, state, zip.

Now i want to create a query that will return all the people in the list, i want to order it by people whos addresses are not in the database first in alphabetical order by last name, and then everyone who is in the the database with an address by alphabetical order by last name. Heres what i have so far:

SELECT fname, lname, phone, street
FROM tblGrooms
ORDER BY street, lname;

Now this will return exactly what i want, but it sorts the second half (the list of names that DO have addresses) by address NOT last name. How can i fix this?

Im sure this is a really simple fix and im just over looking it! Please help!
You could experiment with a UNION Query, something along the lines of:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblEmployee.LastName, tblEmployee.FirstName, tblEmployee.PhoneNumber, tblEmployee.Address
  2. FROM tblEmployee
  3. WHERE tblEmployee.Address Is Null
  4.  UNION SELECT tblEmployee.LastName, tblEmployee.FirstName, tblEmployee.PhoneNumber, tblEmployee.Address
  5. FROM tblEmployee
  6. WHERE tblEmployee.Address Is Not Null
  7. ORDER BY tblEmployee.LastName;
May 12 '07 #3

NeoPa
Expert Mod 15k+
P: 31,489
I am creating a database to store a wedding invite list. I am storing fname, lname, phone, street, city, state, zip.

Now i want to create a query that will return all the people in the list, i want to order it by people whos addresses are not in the database first in alphabetical order by last name, and then everyone who is in the the database with an address by alphabetical order by last name. Heres what i have so far:

Expand|Select|Wrap|Line Numbers
  1. SELECT fname, lname, phone, street
  2. FROM tblGrooms
  3. ORDER BY street, lname;
Now this will return exactly what i want, but it sorts the second half (the list of names that DO have addresses) by address NOT last name. How can i fix this?

Im sure this is a really simple fix and im just over looking it! Please help!
You've only told us half the story.
How can you sort by the last name if the entry doesn't even exist in the database?
If you can express the question accurately, then I'm sure someone can help you with an answer. I suspect it won't be as complicated as using a UNION query in this case, but we can only guess if you don't share the question with us.
May 14 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.