Sorting Query Results - Advice Please

[Dermot]

I have created a table with Firstname and Lastname fields.
Then I created a query to sort alphabetically by Lastname and then Firstname
order.

Some entries do not have a first name and these appear at the top of query
result.

Question
How can I create a condition to place the "Null" Firstname results to the
bottom of the returned list, rather than the top?

 

[fredg]

I have created a table with Firstname and Lastname fields.
Then I created a query to sort alphabetically by Lastname and then Firstname
order.

Some entries do not have a first name and these appear at the top of query
result.

Question
How can I create a condition to place the "Null" Firstname results to the
bottom of the returned list, rather than the top?

Add another column.
SortFirstName:IIf(IsNull([FirstName]),"zzzzzz",[FirstName])

Sort on LastName and this column instead of FirstName.

 

[Dermot]

Hi Fred
Thanks for the reply
Can you explain a little further how the expression works and why the use of
another column.

Thanks in advance

Thanks in advance

I have created a table with Firstname and Lastname fields.
Then I created a query to sort alphabetically by Lastname and then Firstname
order.

Some entries do not have a first name and these appear at the top of query
result.

Question
How can I create a condition to place the "Null" Firstname results to the
bottom of the returned list, rather than the top?

Add another column.
SortFirstName:IIf(IsNull([FirstName]),"zzzzzz",[FirstName])

Sort on LastName and this column instead of FirstName.

 

[John Spencer]

I understood your request slightly different than Fred.

If you want all records sorted by last name (but in two groups)
Last Names with First Names
followed by
Last Names with NO first Name

Insert field in the query in the first position and set it as follows
SortWithFirst: [First Name] is Not Null
Sort By: Ascending

In the ORDER BY clause of the SQL you would have

ORDER BY [FirstName] Is Null, [LastName], [FirstName]

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have created a table with Firstname and Lastname fields.
Then I created a query to sort alphabetically by Lastname and then Firstname
order.

Some entries do not have a first name and these appear at the top of query
result.

Question
How can I create a condition to place the "Null" Firstname results to the
bottom of the returned list, rather than the top?

Add another column.
SortFirstName:IIf(IsNull([FirstName]),"zzzzzz",[FirstName])

Sort on LastName and this column instead of FirstName.

 

[Dermot]

Hi John
Thanks for the reply and explanation.

I have resolved the problem using the information you provided but would
like to ask you for further advice to clarify my understanding.

Quote:
ORDER BY [FirstName] Is Not Null, [LastName], [FirstName]

Can the ORDER BY Clause only be entered directly into the SQL Satement.
I.E View | SQL View and manually enter the details to the clause.

Someone said to me that it can be entered directly from the grid...would
that be via the criteria field?

I understood your request slightly different than Fred.

If you want all records sorted by last name (but in two groups)
Last Names with First Names
followed by
Last Names with NO first Name

Insert field in the query in the first position and set it as follows
SortWithFirst: [First Name] is Not Null
Sort By: Ascending

In the ORDER BY clause of the SQL you would have

ORDER BY [FirstName] Is Not Null, [LastName], [FirstName]

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have created a table with Firstname and Lastname fields.
Then I created a query to sort alphabetically by Lastname and then Firstname
order.

Some entries do not have a first name and these appear at the top of query
result.

Question
How can I create a condition to place the "Null" Firstname results to the
bottom of the returned list, rather than the top?

Add another column.
SortFirstName:IIf(IsNull([FirstName]),"zzzzzz",[FirstName])

Sort on LastName and this column instead of FirstName.

 

[John Spencer]

You can enter an order by in the grid directly.

If you enter it into the grid you would need to enter a calculated field

Field: NameOrder: [FirstName] is not null
Show: Unchecked
Sort: Ascending

No criteria.

If you enter a query in SQL view, you can USUALLY select VIEW: Design from the
menu and see how you would have to do it using the design view. I said
usually because there are some queries that cannot be displayed or constructed
using the design view.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi John
Thanks for the reply and explanation.

I have resolved the problem using the information you provided but would
like to ask you for further advice to clarify my understanding.

Quote:
ORDER BY [FirstName] Is Not Null, [LastName], [FirstName]

Can the ORDER BY Clause only be entered directly into the SQL Satement.
I.E View | SQL View and manually enter the details to the clause.

Someone said to me that it can be entered directly from the grid...would
that be via the criteria field?

I understood your request slightly different than Fred.

If you want all records sorted by last name (but in two groups)
Last Names with First Names
followed by
Last Names with NO first Name

Insert field in the query in the first position and set it as follows
SortWithFirst: [First Name] is Not Null
Sort By: Ascending

In the ORDER BY clause of the SQL you would have

ORDER BY [FirstName] Is Not Null, [LastName], [FirstName]

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

On Fri, 5 Dec 2008 14:23:06 -0800, Dermot wrote:

I have created a table with Firstname and Lastname fields.
Then I created a query to sort alphabetically by Lastname and then Firstname
order.

Some entries do not have a first name and these appear at the top of query
result.

Question
How can I create a condition to place the "Null" Firstname results to the
bottom of the returned list, rather than the top?
Add another column.
SortFirstName:IIf(IsNull([FirstName]),"zzzzzz",[FirstName])

Sort on LastName and this column instead of FirstName.