Multiple Column Sorting...

G

Guest

I need to sort on multiple fields within a query. There are 5 fields that
are being sorted on, the first 3 being asc. The 4th field has an data
entered as an A or D, this telling that when field 1,2,& 3 have the same
data, it sort either A(asc) or D(desc) depending on the 5th field.

I can get the query to run and sort on the first 3 fields, but the
application loses the sorting after this point.

Any ideas?
 
A

Allen Browne

But the 4th column could theoretically have "A" in some rows and "D" in
other rows.

I don't see how the query engine could make sense of the instruction to sort
a column partially ascending and partially descending.
 
G

Guest

Are you saying that you want to sort the 5th field based on the content of
the 4th field?
 
G

Guest

That's what I was thinking...

But I need the query to use the 5th field to sort further depending on what
data is in the 4th field. I know i'm making this as clear as mud, but I'm
stumped.

The only way that I would know how to do it would be some typr of "If"
statement, but I didn't know if SQL had coding for that.
 
G

Guest

Try this --
SELECT Weird_Sort.[1], Weird_Sort.[2], Weird_Sort.[3], Weird_Sort.[4],
IIf([4]="A",[5],Null) AS Expr1, IIf([4]="D",[5],Null) AS Expr2
FROM Weird_Sort
ORDER BY Weird_Sort.[1], Weird_Sort.[2], Weird_Sort.[3],
IIf([4]="A",[5],Null), IIf([4]="D",[5],Null) DESC;

Then in another query concatenate the Expr1 and Expr2 fields.
 
A

Allen Browne

You can use IIf(), the immediate IF in a SQL statement.
But I doubt it will work in the ORDER BY clause like that.

Perhaps you could build the SQL statement dynamically in your code, once you
know which way you want the sort to go. Then apply it as the RecordSource of
your form or report, or even assign it to the SQL property of the QueryDef.
 
G

Guest

I'm trying to use the SQL code that you guys are suggesting, but I keep
getting errors... here is what I have....the fields are to be sorted by

1.) Zip-asc
2.) CART-asc
3.) Lot-asc
4.) Lot_Ord (A or D ---> determines how to sort the 5th field)
5.) DPBC- (determined by Lot_Ord)

The SQL code that I currently have is:

SELECT [Calloway In Order].LAST_NAME, [Calloway In Order].FIRST_NAME,
[Calloway In Order].ADDRESS, [Calloway In Order].CITY, [Calloway In
Order].STATE, [Calloway In Order].ZIP, [Calloway In Order].ZIP4, [Calloway In
Order].CART, [Calloway In Order].DPBC, [Calloway In Order].DPCD, [Calloway In
Order].LOT, [Calloway In Order].LOT_ORD
IIf(Lot_Ord="A",DPBC,Null) AS Expr1, IIf(Lot_ord="D",DPBC,Null) AS Expr2
FROM [Calloway In Order]
ORDER BY [Calloway In Order].ZIP, [Calloway In Order].CART, [Calloway In
Order].LOT;
IIf(Lot_Ord="A",DPBC,Null), IIf(Lot_Ord="D",DPBC,Null) DESC;


Thanks, for all your help with this!!!
 
G

Guest

First I see some bad puncuation.
You need a comma --
[Calloway In Order].LOT_ORD, IIf(Lot_Ord="A",DPBC,Null) AS Expr1,
IIf(Lot_ord="D",DPBC,Null) AS Expr2

You need to remove semicolon --
[Calloway In Order].CART, [Calloway In Order].LOT
IIf(Lot_Ord="A",DPBC,Null), IIf(Lot_Ord="D",DPBC,Null) DESC;


Outatym said:
I'm trying to use the SQL code that you guys are suggesting, but I keep
getting errors... here is what I have....the fields are to be sorted by

1.) Zip-asc
2.) CART-asc
3.) Lot-asc
4.) Lot_Ord (A or D ---> determines how to sort the 5th field)
5.) DPBC- (determined by Lot_Ord)

The SQL code that I currently have is:

SELECT [Calloway In Order].LAST_NAME, [Calloway In Order].FIRST_NAME,
[Calloway In Order].ADDRESS, [Calloway In Order].CITY, [Calloway In
Order].STATE, [Calloway In Order].ZIP, [Calloway In Order].ZIP4, [Calloway In
Order].CART, [Calloway In Order].DPBC, [Calloway In Order].DPCD, [Calloway In
Order].LOT, [Calloway In Order].LOT_ORD
IIf(Lot_Ord="A",DPBC,Null) AS Expr1, IIf(Lot_ord="D",DPBC,Null) AS Expr2
FROM [Calloway In Order]
ORDER BY [Calloway In Order].ZIP, [Calloway In Order].CART, [Calloway In
Order].LOT;
IIf(Lot_Ord="A",DPBC,Null), IIf(Lot_Ord="D",DPBC,Null) DESC;


Thanks, for all your help with this!!!

Allen Browne said:
You can use IIf(), the immediate IF in a SQL statement.
But I doubt it will work in the ORDER BY clause like that.

Perhaps you could build the SQL statement dynamically in your code, once you
know which way you want the sort to go. Then apply it as the RecordSource of
your form or report, or even assign it to the SQL property of the QueryDef.
Click to expand...
Click to expand...
 
G

Guest

I find this to work.
SELECT [Calloway In Order].LAST_NAME, [Calloway In Order].FIRST_NAME,
[Calloway In Order].ADDRESS, [Calloway In Order].CITY, [Calloway In
Order].STATE, [Calloway In Order].ZIP, [Calloway In Order].ZIP4, [Calloway In
Order].CART, [Calloway In Order].DPBC, [Calloway In Order].DPCD, [Calloway In
Order].LOT, [Calloway In Order].LOT_ORD, IIf(Lot_Ord="A",DPBC,Null) AS Expr1,
IIf(Lot_ord="D",DPBC,Null) AS Expr2
FROM [Calloway In Order]
ORDER BY [Calloway In Order].ZIP, [Calloway In Order].CART, [Calloway In
Order].LOT, IIf(Lot_Ord="A",DPBC,Null), IIf(Lot_Ord="D",DPBC,Null) DESC;


Outatym said:
I'm trying to use the SQL code that you guys are suggesting, but I keep
getting errors... here is what I have....the fields are to be sorted by

1.) Zip-asc
2.) CART-asc
3.) Lot-asc
4.) Lot_Ord (A or D ---> determines how to sort the 5th field)
5.) DPBC- (determined by Lot_Ord)

The SQL code that I currently have is:

SELECT [Calloway In Order].LAST_NAME, [Calloway In Order].FIRST_NAME,
[Calloway In Order].ADDRESS, [Calloway In Order].CITY, [Calloway In
Order].STATE, [Calloway In Order].ZIP, [Calloway In Order].ZIP4, [Calloway In
Order].CART, [Calloway In Order].DPBC, [Calloway In Order].DPCD, [Calloway In
Order].LOT, [Calloway In Order].LOT_ORD
IIf(Lot_Ord="A",DPBC,Null) AS Expr1, IIf(Lot_ord="D",DPBC,Null) AS Expr2
FROM [Calloway In Order]
ORDER BY [Calloway In Order].ZIP, [Calloway In Order].CART, [Calloway In
Order].LOT;
IIf(Lot_Ord="A",DPBC,Null), IIf(Lot_Ord="D",DPBC,Null) DESC;


Thanks, for all your help with this!!!

Allen Browne said:
You can use IIf(), the immediate IF in a SQL statement.
But I doubt it will work in the ORDER BY clause like that.

Perhaps you could build the SQL statement dynamically in your code, once you
know which way you want the sort to go. Then apply it as the RecordSource of
your form or report, or even assign it to the SQL property of the QueryDef.
Click to expand...
Click to expand...
 
G

Guest

I'm trying to run the code that you have suggested, but it's asking for me to
input a parameter value....

it says "Calloway In" then gives me a text input area...

KARL DEWEY said:
I find this to work.
SELECT [Calloway In Order].LAST_NAME, [Calloway In Order].FIRST_NAME,
[Calloway In Order].ADDRESS, [Calloway In Order].CITY, [Calloway In
Order].STATE, [Calloway In Order].ZIP, [Calloway In Order].ZIP4, [Calloway In
Order].CART, [Calloway In Order].DPBC, [Calloway In Order].DPCD, [Calloway In
Order].LOT, [Calloway In Order].LOT_ORD, IIf(Lot_Ord="A",DPBC,Null) AS Expr1,
IIf(Lot_ord="D",DPBC,Null) AS Expr2
FROM [Calloway In Order]
ORDER BY [Calloway In Order].ZIP, [Calloway In Order].CART, [Calloway In
Order].LOT, IIf(Lot_Ord="A",DPBC,Null), IIf(Lot_Ord="D",DPBC,Null) DESC;


Outatym said:
I'm trying to use the SQL code that you guys are suggesting, but I keep
getting errors... here is what I have....the fields are to be sorted by

1.) Zip-asc
2.) CART-asc
3.) Lot-asc
4.) Lot_Ord (A or D ---> determines how to sort the 5th field)
5.) DPBC- (determined by Lot_Ord)

The SQL code that I currently have is:

SELECT [Calloway In Order].LAST_NAME, [Calloway In Order].FIRST_NAME,
[Calloway In Order].ADDRESS, [Calloway In Order].CITY, [Calloway In
Order].STATE, [Calloway In Order].ZIP, [Calloway In Order].ZIP4, [Calloway In
Order].CART, [Calloway In Order].DPBC, [Calloway In Order].DPCD, [Calloway In
Order].LOT, [Calloway In Order].LOT_ORD
IIf(Lot_Ord="A",DPBC,Null) AS Expr1, IIf(Lot_ord="D",DPBC,Null) AS Expr2
FROM [Calloway In Order]
ORDER BY [Calloway In Order].ZIP, [Calloway In Order].CART, [Calloway In
Order].LOT;
IIf(Lot_Ord="A",DPBC,Null), IIf(Lot_Ord="D",DPBC,Null) DESC;


Thanks, for all your help with this!!!

Allen Browne said:
You can use IIf(), the immediate IF in a SQL statement.
But I doubt it will work in the ORDER BY clause like that.

Perhaps you could build the SQL statement dynamically in your code, once you
know which way you want the sort to go. Then apply it as the RecordSource of
your form or report, or even assign it to the SQL property of the QueryDef.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

That's what I was thinking...

But I need the query to use the 5th field to sort further depending on
what
data is in the 4th field. I know i'm making this as clear as mud, but I'm
stumped.

The only way that I would know how to do it would be some typr of "If"
statement, but I didn't know if SQL had coding for that.

:

But the 4th column could theoretically have "A" in some rows and "D" in
other rows.

I don't see how the query engine could make sense of the instruction to
sort
a column partially ascending and partially descending.

I need to sort on multiple fields within a query. There are 5 fields
that
are being sorted on, the first 3 being asc. The 4th field has an data
entered as an A or D, this telling that when field 1,2,& 3 have the
same
data, it sort either A(asc) or D(desc) depending on the 5th field.

I can get the query to run and sort on the first 3 fields, but the
application loses the sorting after this point.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Sometimes the copying and pasting from the post add a line feed (RETURN).

Check the SQL you pasted - 2nd, 4th, and 6th line splits the table name.
Like this --
xx xxxx xxx [Calloway In
Order].

That is why most folks only use underscore instead of spaces.


Outatym said:
I'm trying to run the code that you have suggested, but it's asking for me to
input a parameter value....

it says "Calloway In" then gives me a text input area...

KARL DEWEY said:
I find this to work.
SELECT [Calloway In Order].LAST_NAME, [Calloway In Order].FIRST_NAME,
[Calloway In Order].ADDRESS, [Calloway In Order].CITY, [Calloway In
Order].STATE, [Calloway In Order].ZIP, [Calloway In Order].ZIP4, [Calloway In
Order].CART, [Calloway In Order].DPBC, [Calloway In Order].DPCD, [Calloway In
Order].LOT, [Calloway In Order].LOT_ORD, IIf(Lot_Ord="A",DPBC,Null) AS Expr1,
IIf(Lot_ord="D",DPBC,Null) AS Expr2
FROM [Calloway In Order]
ORDER BY [Calloway In Order].ZIP, [Calloway In Order].CART, [Calloway In
Order].LOT, IIf(Lot_Ord="A",DPBC,Null), IIf(Lot_Ord="D",DPBC,Null) DESC;


Outatym said:
I'm trying to use the SQL code that you guys are suggesting, but I keep
getting errors... here is what I have....the fields are to be sorted by

1.) Zip-asc
2.) CART-asc
3.) Lot-asc
4.) Lot_Ord (A or D ---> determines how to sort the 5th field)
5.) DPBC- (determined by Lot_Ord)

The SQL code that I currently have is:

SELECT [Calloway In Order].LAST_NAME, [Calloway In Order].FIRST_NAME,
[Calloway In Order].ADDRESS, [Calloway In Order].CITY, [Calloway In
Order].STATE, [Calloway In Order].ZIP, [Calloway In Order].ZIP4, [Calloway In
Order].CART, [Calloway In Order].DPBC, [Calloway In Order].DPCD, [Calloway In
Order].LOT, [Calloway In Order].LOT_ORD
IIf(Lot_Ord="A",DPBC,Null) AS Expr1, IIf(Lot_ord="D",DPBC,Null) AS Expr2
FROM [Calloway In Order]
ORDER BY [Calloway In Order].ZIP, [Calloway In Order].CART, [Calloway In
Order].LOT;
IIf(Lot_Ord="A",DPBC,Null), IIf(Lot_Ord="D",DPBC,Null) DESC;


Thanks, for all your help with this!!!

:

You can use IIf(), the immediate IF in a SQL statement.
But I doubt it will work in the ORDER BY clause like that.

Perhaps you could build the SQL statement dynamically in your code, once you
know which way you want the sort to go. Then apply it as the RecordSource of
your form or report, or even assign it to the SQL property of the QueryDef.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

That's what I was thinking...

But I need the query to use the 5th field to sort further depending on
what
data is in the 4th field. I know i'm making this as clear as mud, but I'm
stumped.

The only way that I would know how to do it would be some typr of "If"
statement, but I didn't know if SQL had coding for that.

:

But the 4th column could theoretically have "A" in some rows and "D" in
other rows.

I don't see how the query engine could make sense of the instruction to
sort
a column partially ascending and partially descending.

I need to sort on multiple fields within a query. There are 5 fields
that
are being sorted on, the first 3 being asc. The 4th field has an data
entered as an A or D, this telling that when field 1,2,& 3 have the
same
data, it sort either A(asc) or D(desc) depending on the 5th field.

I can get the query to run and sort on the first 3 fields, but the
application loses the sorting after this point.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query does not return First or Last record from subquery 1
Access MSACCESS - Sorting report based on field calculated in the report 0
Need help sorting a query 2
Split data in field on character return 0
Sorting a table by concatenating several fields in the same table 3
Sorting on the fly 1
Concatenated Field - Sort Order 4
Sorting a table using hidden field 6

Top