Sorting Fields

[Haggr1 via AccessMonster.com]

I have a query that returns [DaysOld],[Status]

[DaysOld] is a number ie. "9" showing how old that job is.

[Status] is "sales", "stock", "old sales" and a few other


I need to be able to "Sort" by "Sales oldest to newest" and after "Stock
oldest to newest" and so on.

I can think of a few ways for assigning of new field New Field:IIF([sales],2,
"A") Even writing that reveals a lot of problems. Thanks

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Well you could use an ORDER BY clause like this:

ORDER BY [Status], DaysOld DESC

But, it wouldn't have the "sales" Status first - "old sales" would be
first, alphabetically.

You could have an order_by column in your table and just assign a number
to each Status value:

order_by Status
1 sales
2 stock
3 old sales
... etc. ...

Then just use:

ORDER BY order_by, DaysOld DESC

You could also create a order_by column in the SELECT clause and then
sort by that column:

SELECT Switch([Status]="sales",1,
[Status]="stock",2,
[Status]="old sales",3) As order_by,
...... etc. ...
FROM ...
WHERE ...
ORDER BY 1, DaysOld DESC

The ORDER BY 1 means sort by the first column value. This way you don't
have to enter the complete Switch() function in the ORDER BY clause -
you can if you want.

Be sure to add the different Status values and the sort order number to
the Switch() function. For more info on Switch see the VBA help article
Switch Function.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSWUxnYechKqOuFEgEQJklwCfcFfm51Vmib3tvKaoEK4vtM/UxggAmwR1
MEPguDQpDbUpF8vvguDM/0TB
=Uyr7
-----END PGP SIGNATURE-----

 

[Haggr1 via AccessMonster.com]

Here is the query. can you show me how I would add what you wrote. [Which]
the field that needs sorted

SELECT Table1.[Job Number], Table1.SetterCompany, Table1.[Date To], Table1.
[Date From], Table1.Amount, Table1.[File Number], TodayToAppendTo.Item,
TodayToAppendTo.Size, TodayToAppendTo.Avl, TodayToAppendTo.[Req'd],
TodayToAppendTo.[Curr Routing], TodayToAppendTo.Sts, TodayToAppendTo.Field17,
TodayToAppendTo.Field18, TodayToAppendTo.Field19, TodayToAppendTo.Left,
TodayToAppendTo.Field21, TodayToAppendTo.Field22, TodayToAppendTo.Field23,
TodayToAppendTrdered, TodayToAppendTo.Field25, TodayToAppendTo.Style,
TodayToAppendTo.Due, TodayToAppendTo.Type, TodayToAppendTo.Which, Table1.Text
FROM (QryTodayImportDueFileNumber LEFT JOIN Table1 ON
QryTodayImportDueFileNumber.[MaxOfFile Number] = Table1.[File Number]) LEFT
JOIN TodayToAppendTo ON QryTodayImportDueFileNumber.Job = TodayToAppendTo.
Job;

Well you could use an ORDER BY clause like this:

ORDER BY [Status], DaysOld DESC

But, it wouldn't have the "sales" Status first - "old sales" would be
first, alphabetically.

You could have an order_by column in your table and just assign a number
to each Status value:

order_by Status
1 sales
2 stock
3 old sales
... etc. ...

Then just use:

ORDER BY order_by, DaysOld DESC

You could also create a order_by column in the SELECT clause and then
sort by that column:

SELECT Switch([Status]="sales",1,
[Status]="stock",2,
[Status]="old sales",3) As order_by,
...... etc. ...
FROM ...
WHERE ...
ORDER BY 1, DaysOld DESC

The ORDER BY 1 means sort by the first column value. This way you don't
have to enter the complete Switch() function in the ORDER BY clause -
you can if you want.

Be sure to add the different Status values and the sort order number to
the Switch() function. For more info on Switch see the VBA help article
Switch Function.

I have a query that returns [DaysOld],[Status]

[quoted text clipped - 7 lines]

I can think of a few ways for assigning of new field New Field:IIF([sales],2,
"A") Even writing that reveals a lot of problems. Thanks

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You stated in your previous post that the column name was [Status].
Does the column [Which] hold the same data?

Also, I'd need to know what the possible data items that can be in the
column ("sales", "stock", "old sales", etc.).

Why can't you just use the Switch() function as I demonstrated in my
previous post? Example: (your SELECT clause)

.....

TodayToAppendTo.Due, TodayToAppendTo.Type,

Switch(TodayToAppendTo.Which="Sales",1 ,
TodayToAppendTo.Which="Stock",2 ,
TodayToAppendTo.Which="old sales",3 ,

.... the other possible data values in [Which] ....

) As TheNameYouWantHere ,

Table1.Text

.....

Be sure to change TheNameYouWantHere to the column name you want.

Remember to remove ".... the other possible data values in [Which] ...."
from the function. And, remember the closing parenthesis on the
function - before the column name alias.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSWZrrIechKqOuFEgEQKGUgCgl/t9b50nkNJsN/Bo3mVC7CzO41EAnjLe
HcD0Y3161jQb5uGofDU+ev0Z
=SvrL
-----END PGP SIGNATURE-----

Here is the query. can you show me how I would add what you wrote. [Which]
the field that needs sorted

SELECT Table1.[Job Number], Table1.SetterCompany, Table1.[Date To], Table1.
[Date From], Table1.Amount, Table1.[File Number], TodayToAppendTo.Item,
TodayToAppendTo.Size, TodayToAppendTo.Avl, TodayToAppendTo.[Req'd],
TodayToAppendTo.[Curr Routing], TodayToAppendTo.Sts, TodayToAppendTo.Field17,
TodayToAppendTo.Field18, TodayToAppendTo.Field19, TodayToAppendTo.Left,
TodayToAppendTo.Field21, TodayToAppendTo.Field22, TodayToAppendTo.Field23,
TodayToAppendTrdered, TodayToAppendTo.Field25, TodayToAppendTo.Style,
TodayToAppendTo.Due, TodayToAppendTo.Type, TodayToAppendTo.Which, Table1.Text
FROM (QryTodayImportDueFileNumber LEFT JOIN Table1 ON
QryTodayImportDueFileNumber.[MaxOfFile Number] = Table1.[File Number]) LEFT
JOIN TodayToAppendTo ON QryTodayImportDueFileNumber.Job = TodayToAppendTo.
Job;

Well you could use an ORDER BY clause like this:

ORDER BY [Status], DaysOld DESC

But, it wouldn't have the "sales" Status first - "old sales" would be
first, alphabetically.

You could have an order_by column in your table and just assign a number
to each Status value:

order_by Status
1 sales
2 stock
3 old sales
... etc. ...

Then just use:

ORDER BY order_by, DaysOld DESC

You could also create a order_by column in the SELECT clause and then
sort by that column:

SELECT Switch([Status]="sales",1,
[Status]="stock",2,
[Status]="old sales",3) As order_by,
...... etc. ...
FROM ...
WHERE ...
ORDER BY 1, DaysOld DESC

The ORDER BY 1 means sort by the first column value. This way you don't
have to enter the complete Switch() function in the ORDER BY clause -
you can if you want.

Be sure to add the different Status values and the sort order number to
the Switch() function. For more info on Switch see the VBA help article
Switch Function.

I have a query that returns [DaysOld],[Status]

[quoted text clipped - 7 lines]

I can think of a few ways for assigning of new field New Field:IIF([sales],2,
"A") Even writing that reveals a lot of problems. Thanks

 

[Haggr1 via AccessMonster.com]

Sorry, I thought I named that "Status" But it is [Which]

When I run that Query I would like the returns to be sorted by the data in
that column is this order:


1 "Today"
2 "(Day if Week) ie "Monday", "Tuesday" ...
3 "DotCom"
4 "3DaysOld"
5 "Stock"
6 "OldStock"

Thanks, As you can probably tell from that "Query" programing is not my main
job here!!!!!

You stated in your previous post that the column name was [Status].
Does the column [Which] hold the same data?

Also, I'd need to know what the possible data items that can be in the
column ("sales", "stock", "old sales", etc.).

Why can't you just use the Switch() function as I demonstrated in my
previous post? Example: (your SELECT clause)

....

TodayToAppendTo.Due, TodayToAppendTo.Type,

Switch(TodayToAppendTo.Which="Sales",1 ,
TodayToAppendTo.Which="Stock",2 ,
TodayToAppendTo.Which="old sales",3 ,

.... the other possible data values in [Which] ....

) As TheNameYouWantHere ,

Table1.Text

....

Be sure to change TheNameYouWantHere to the column name you want.

Remember to remove ".... the other possible data values in [Which] ...."
from the function. And, remember the closing parenthesis on the
function - before the column name alias.

Here is the query. can you show me how I would add what you wrote. [Which]
the field that needs sorted

[quoted text clipped - 55 lines]

I can think of a few ways for assigning of new field New Field:IIF([sales],2,
"A") Even writing that reveals a lot of problems. Thanks

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Make the Switch() function like this (I put different data items on
different lines just for clarity - you can put it all on one line):

SELECT
Switch(TodayToAppendTo.Which="Today",1,
TodayToAppendTo.Which
IN ("Monday", "Tuesday", "Wednesday", "Thursday", "Friday"), 2,
TodayToAppendTo.Which="DotCom",3,
TodayToAppendTo.Which="3DaysOld",4,
TodayToAppendTo.Which="Stock",5
TodayToAppendTo.Which="OldStock",6) As SortOrder ,

... the rest of the query ...

The IN () operator returns True if the value of [Which] is one of the
items in the list of weekday names, which makes the Switch() function
return 2 as the value of the SortOrder.

Make the ORDER BY clause like this:

ORDER BY 1, DaysOld DESC

I don't know the column name for the "DaysOld" you gave in your earlier
posts. You'll have to substitute it for what I have.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSWa3aYechKqOuFEgEQKJ9gCg1yN8vyU21fr+u4IttZDPCNaNXy4AoOBa
o1J0R6LZzg2Cl/goN9MTKVoe
=EWya
-----END PGP SIGNATURE-----

Sorry, I thought I named that "Status" But it is [Which]

When I run that Query I would like the returns to be sorted by the data in
that column is this order:


1 "Today"
2 "(Day if Week) ie "Monday", "Tuesday" ...
3 "DotCom"
4 "3DaysOld"
5 "Stock"
6 "OldStock"

Thanks, As you can probably tell from that "Query" programing is not my main
job here!!!!!

You stated in your previous post that the column name was [Status].
Does the column [Which] hold the same data?

Also, I'd need to know what the possible data items that can be in the
column ("sales", "stock", "old sales", etc.).

Why can't you just use the Switch() function as I demonstrated in my
previous post? Example: (your SELECT clause)

....

TodayToAppendTo.Due, TodayToAppendTo.Type,

Switch(TodayToAppendTo.Which="Sales",1 ,
TodayToAppendTo.Which="Stock",2 ,
TodayToAppendTo.Which="old sales",3 ,

.... the other possible data values in [Which] ....

) As TheNameYouWantHere ,

Table1.Text

....

Be sure to change TheNameYouWantHere to the column name you want.

Remember to remove ".... the other possible data values in [Which] ...."
from the function. And, remember the closing parenthesis on the
function - before the column name alias.

Here is the query. can you show me how I would add what you wrote. [Which]
the field that needs sorted

[quoted text clipped - 55 lines]

I can think of a few ways for assigning of new field New Field:IIF([sales],2,
"A") Even writing that reveals a lot of problems. Thanks