manual sort in queries

[Guest]

I have a simple query where I want to sort the records based upon one fields
data and not ascending or descending.

My SQL is:

SELECT [Flight Roster].Rank, [Flight Roster].[Last Name], [Flight
Roster].Shift, [Flight Roster].AFSC
FROM [Flight Roster]
WHERE ((([Flight Roster].Status)="1")) OR ((([Flight
Roster].Status)="Orders") AND (([Flight Roster].[Last Duty Day])>Now()))
ORDER BY [Flight Roster].Shift, [Flight Roster].AFSC;

I first want to order it by [Flight Roster].Shift by ascending and then by
[Flight Roster].AFSC but in a specific order based upon data stored within
AFSC.

I want records with 2A371 ordered first, then 2A351A, then 2A331A, then
2A351B, then 2A331B, then 2A656, then 2A671A.


For example for all shift 1, I want the AFSC's in the order I want, then for
Shift 2, I also want AFSC's in the same order but all after shift 1.

Is this even possible?

If you need more clarification, please ask.

Thanks for all you help

Dan

 

[Ken Snell \(MVP\)]

The "trick" is to have a calculated field that is assigned a value based on
the desired sorting order of the original field's values, then sort on the
calculated field. For example:

SELECT [Flight Roster].Rank, [Flight Roster].[Last Name], [Flight
Roster].Shift, [Flight Roster].AFSC
FROM [Flight Roster]
WHERE ((([Flight Roster].Status)="1")) OR ((([Flight
Roster].Status)="Orders") AND (([Flight Roster].[Last Duty Day])>Now()))
ORDER BY [Flight Roster].Shift,
(IIf([Flight Roster].AFSC="2A371",1,IIf([Flight Roster].AFSC="2A351A",2,
IIf([Flight Roster].AFSC="2A331",3,IIf([Flight Roster].AFSC="2A351B",4,
IIf([Flight Roster].AFSC="2A331B",5,IIf([Flight Roster].AFSC="2A656",6,
IIf([Flight Roster].AFSC="2A671A",7,8))))))));

The above shows how you can do it in a query. A better way would be to
create a table (name it tblAFSCOrderBy), with these fields:
fldAFSC (text) primary key
fldOrderNum (long integer)

Then populate this table with the desired data:
fldAFSC fldOrderNum
--------- --------------
2A371 1
2A351A 2
2A331A 3
2A351B 4
2A331B 5
2A656 6
2A671A 7

Then you change your query to this:

SELECT [Flight Roster].Rank, [Flight Roster].[Last Name], [Flight
Roster].Shift, [Flight Roster].AFSC
FROM [Flight Roster] LEFT JOIN tblAFSCOrderBy
ON [Flight Roster].AFSC=tblAFSCOrderBy.fldAFSC
WHERE ((([Flight Roster].Status)="1")) OR ((([Flight
Roster].Status)="Orders") AND (([Flight Roster].[Last Duty Day])>Now()))
ORDER BY [Flight Roster].Shift,
Nz(tblAFSCOrderBy.fldOrderNum,10000);

 

[Guest]

Ken,

I used the second and better suggestion and it worked like a champ.

Thank You for your time.

Dan

The "trick" is to have a calculated field that is assigned a value based on
the desired sorting order of the original field's values, then sort on the
calculated field. For example:

SELECT [Flight Roster].Rank, [Flight Roster].[Last Name], [Flight
Roster].Shift, [Flight Roster].AFSC
FROM [Flight Roster]
WHERE ((([Flight Roster].Status)="1")) OR ((([Flight
Roster].Status)="Orders") AND (([Flight Roster].[Last Duty Day])>Now()))
ORDER BY [Flight Roster].Shift,
(IIf([Flight Roster].AFSC="2A371",1,IIf([Flight Roster].AFSC="2A351A",2,
IIf([Flight Roster].AFSC="2A331",3,IIf([Flight Roster].AFSC="2A351B",4,
IIf([Flight Roster].AFSC="2A331B",5,IIf([Flight Roster].AFSC="2A656",6,
IIf([Flight Roster].AFSC="2A671A",7,8))))))));

The above shows how you can do it in a query. A better way would be to
create a table (name it tblAFSCOrderBy), with these fields:
fldAFSC (text) primary key
fldOrderNum (long integer)

Then populate this table with the desired data:
fldAFSC fldOrderNum
--------- --------------
2A371 1
2A351A 2
2A331A 3
2A351B 4
2A331B 5
2A656 6
2A671A 7

Then you change your query to this:

SELECT [Flight Roster].Rank, [Flight Roster].[Last Name], [Flight
Roster].Shift, [Flight Roster].AFSC
FROM [Flight Roster] LEFT JOIN tblAFSCOrderBy
ON [Flight Roster].AFSC=tblAFSCOrderBy.fldAFSC
WHERE ((([Flight Roster].Status)="1")) OR ((([Flight
Roster].Status)="Orders") AND (([Flight Roster].[Last Duty Day])>Now()))
ORDER BY [Flight Roster].Shift,
Nz(tblAFSCOrderBy.fldOrderNum,10000);


--

Ken Snell
<MS ACCESS MVP>

I have a simple query where I want to sort the records based upon one
fields
data and not ascending or descending.

My SQL is:

SELECT [Flight Roster].Rank, [Flight Roster].[Last Name], [Flight
Roster].Shift, [Flight Roster].AFSC
FROM [Flight Roster]
WHERE ((([Flight Roster].Status)="1")) OR ((([Flight
Roster].Status)="Orders") AND (([Flight Roster].[Last Duty Day])>Now()))
ORDER BY [Flight Roster].Shift, [Flight Roster].AFSC;

I first want to order it by [Flight Roster].Shift by ascending and then by
[Flight Roster].AFSC but in a specific order based upon data stored within
AFSC.

I want records with 2A371 ordered first, then 2A351A, then 2A331A, then
2A351B, then 2A331B, then 2A656, then 2A671A.


For example for all shift 1, I want the AFSC's in the order I want, then
for
Shift 2, I also want AFSC's in the same order but all after shift 1.

Is this even possible?

If you need more clarification, please ask.

Thanks for all you help

Dan

 

[Ken Snell \(MVP\)]

You're welcome.