Sorting Query on 2 fields

[dhstein]

I want a query to sort on 2 fields like Excel does (sort first on field 1
then on field 2).

1) Is there a simple way to do this?

2) If not, I can concatenate the 2 fields (they're both text fields) and
sort on that column. In that case, can I use the "&" operator in the query -
or do I need to write a function to concatenate the fields.

Thanks for any help you can provide

 

[Tom van Stiphout]

On Sun, 25 Jan 2009 09:33:01 -0800, dhstein

Sure you can. In the Design grid select Ascending in the Sort line of
both of your columns. The sort works left to right, so be sure to
arrange your columns accordingly.

in SQL the statement would be something like:
select * from myTable
order by myFirstField, mySecondField

-Tom.
Microsoft Access MVP

 

[pietlinden]

I want a query to sort on 2 fields like Excel does  (sort first on field 1
then on field 2).

1)  Is there a simple way to do this?

2)  If not, I can concatenate the 2 fields (they're both text fields) and
sort on that column.  In that case, can I use the "&" operator in the query -
or do I need to write a function to concatenate the fields.

Thanks for any help you can provide

1.
SELECT FieldA, FieldB
FROM SomeTable
ORDER BY FieldA, FieldB;

and if you want to sort by fields that aren't being shown...

SELECT FieldA, FieldB
FROM SomeTable
ORDER BY FieldC ASC, FieldD DESC;

2. not necessary.

 

[MikeJohnB]

With a query in design view, drag to column you want sorted first to the left
of the column you want sorted second. In the sort row, select ascending or
decesending as required, the result will be sorted in the order you require.

Also, to concatenate two columns into one, insert a column

FullName: [FirstName] & " " &[LastName] (Place in the fireld row will
Concatenate two fields with a space as a sort column)

This Has given you the option of both your questions, I hope this helps
you??????
Kindest Regards
Mike B

 

[dhstein]

Thanks Tom, piet and Mike

I ended up using the concatenate fields method so as not to have to
rearrange the columns - it worked great.

With a query in design view, drag to column you want sorted first to the left
of the column you want sorted second. In the sort row, select ascending or
decesending as required, the result will be sorted in the order you require.

Also, to concatenate two columns into one, insert a column

FullName: [FirstName] & " " &[LastName] (Place in the fireld row will
Concatenate two fields with a space as a sort column)

This Has given you the option of both your questions, I hope this helps
you??????
Kindest Regards
Mike B
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

I want a query to sort on 2 fields like Excel does (sort first on field 1
then on field 2).

1) Is there a simple way to do this?

2) If not, I can concatenate the 2 fields (they're both text fields) and
sort on that column. In that case, can I use the "&" operator in the query -
or do I need to write a function to concatenate the fields.

Thanks for any help you can provide

 

[Tom van Stiphout]

On Sun, 25 Jan 2009 11:08:01 -0800, dhstein

It seemed you wanted a quick answer, but here is the somewhat longer
answer: if you don't want to rearrange columns (rarely a requirement),
you can drag the SAME columns to the grid twice, put them in the sort
order, uncheck the visible box, and set the Ascending property.
Concatenating is typically slower than sorting the underlying columns,
especially if you have an appropriate index.

-Tom.
Microsoft Access MVP

Thanks Tom, piet and Mike

I ended up using the concatenate fields method so as not to have to
rearrange the columns - it worked great.

With a query in design view, drag to column you want sorted first to the left
of the column you want sorted second. In the sort row, select ascending or
decesending as required, the result will be sorted in the order you require.

Also, to concatenate two columns into one, insert a column

FullName: [FirstName] & " " &[LastName] (Place in the fireld row will
Concatenate two fields with a space as a sort column)

This Has given you the option of both your questions, I hope this helps
you??????
Kindest Regards
Mike B
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B

I want a query to sort on 2 fields like Excel does (sort first on field 1
then on field 2).

1) Is there a simple way to do this?

2) If not, I can concatenate the 2 fields (they're both text fields) and
sort on that column. In that case, can I use the "&" operator in the query -
or do I need to write a function to concatenate the fields.

Thanks for any help you can provide

 

[Bob Quintal]

Thanks Tom, piet and Mike

I ended up using the concatenate fields method so as not to have
to rearrange the columns - it worked great.

Note that a concatenated field sort is much slower than the sort
using two columns, if the first or both columns are indexed. You may
not see any difference with a few hundred rows, but when you have
750,000 you can go for coffee and a smoke before the sort will
finish, when sorting on a concatenated field.

Q

With a query in design view, drag to column you want sorted first
to the left of the column you want sorted second. In the sort
row, select ascending or decesending as required, the result will
be sorted in the order you require.

Also, to concatenate two columns into one, insert a column

FullName: [FirstName] & " " &[LastName] (Place in the fireld row
will Concatenate two fields with a space as a sort column)

This Has given you the option of both your questions, I hope this
helps you??????
Kindest Regards
Mike B
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have
recieved. Kindest Regards Mike B

I want a query to sort on 2 fields like Excel does (sort first
on field 1 then on field 2).

1) Is there a simple way to do this?

2) If not, I can concatenate the 2 fields (they're both text
fields) and sort on that column. In that case, can I use the
"&" operator in the query - or do I need to write a function to
concatenate the fields.

Thanks for any help you can provide

 

[dhstein]

Thanks for the additional info on the pros and cons of concatenation. I
liked your idea Tomand made two more columns to sort on and not display.

Thanks Tom, piet and Mike

I ended up using the concatenate fields method so as not to have
to rearrange the columns - it worked great.

Note that a concatenated field sort is much slower than the sort
using two columns, if the first or both columns are indexed. You may
not see any difference with a few hundred rows, but when you have
750,000 you can go for coffee and a smoke before the sort will
finish, when sorting on a concatenated field.

Q

With a query in design view, drag to column you want sorted first
to the left of the column you want sorted second. In the sort
row, select ascending or decesending as required, the result will
be sorted in the order you require.

Also, to concatenate two columns into one, insert a column

FullName: [FirstName] & " " &[LastName] (Place in the fireld row
will Concatenate two fields with a space as a sort column)

This Has given you the option of both your questions, I hope this
helps you??????
Kindest Regards
Mike B
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have
recieved. Kindest Regards Mike B


:

I want a query to sort on 2 fields like Excel does (sort first
on field 1 then on field 2).

1) Is there a simple way to do this?

2) If not, I can concatenate the 2 fields (they're both text
fields) and sort on that column. In that case, can I use the
"&" operator in the query - or do I need to write a function to
concatenate the fields.

Thanks for any help you can provide