Prev: How can I refresh the master form from a subform?
Next: Delete Query Help
From: CBender on 11 May 2010 12:03
I am trying to Rank a field based on multiple sort criteria. My query sorts
correctly, but the Ranking assignments are not correct. Could someone please
review my SQL query and let me know how to correct this problem?

I need to sort Descending by [Type2] AS WELL AS Descending by [Ext B/O]



I used the following to assign my [Type2] field data:

Type2: IIf([All BO]>=100 And [Host DN]>=20000,3,(IIf([Host
DN]>=20000,2,(IIf([All BO]>=100,1)))))


I am currently using the following to create my Ranking:

Rank: (SELECT Count([VT].[Ext BO]) FROM [tmp_tbl_Chart5a_1] AS VT WHERE
VT.[Ext BO] > tmp_tbl_Chart5a_1.[Ext BO])+1


I know the Ranking I used sorts ONLY on the [Ext BO] field but I am not a
coder and do not know how to modify the format of my query to get what I need.


Can someone please help??


Thanks,
--
Chip
From: CBender on 11 May 2010 12:23
Sorry, After reading my post I feel I need to clarify a couple of things.

I need the ranking to sort on the [Type2] value, then by [Host DN] value,
then by the [All BO] value.

This should provide somewhat of a stepping effect for the reporting as shown
below:

[Rank] [Host DN] [All BO]
[Type2]
1 3,293,090.80 458
3
2 173,563.20 214
3
3 1,029,110.22 8
2
4 877,394.54 6
2
5 3,090.80 218
1
6 2,450.38 188
1
7 15,578.32 65
7 15,578.32 65
8 9,824.67 57
9 548.14 38

I know that where duplicate values are reported duplicate ranking will be
listed and this is what I want.

I hope this clarified what I am looking to try and do.



Thanks,

--
Chip


"CBender" wrote:

> I am trying to Rank a field based on multiple sort criteria. My query sorts
> correctly, but the Ranking assignments are not correct. Could someone please
> review my SQL query and let me know how to correct this problem?
>
> I need to sort Descending by [Type2] AS WELL AS Descending by [Ext B/O]
>
>
>
> I used the following to assign my [Type2] field data:
>
> Type2: IIf([All BO]>=100 And [Host DN]>=20000,3,(IIf([Host
> DN]>=20000,2,(IIf([All BO]>=100,1)))))
>
>
> I am currently using the following to create my Ranking:
>
> Rank: (SELECT Count([VT].[Ext BO]) FROM [tmp_tbl_Chart5a_1] AS VT WHERE
> VT.[Ext BO] > tmp_tbl_Chart5a_1.[Ext BO])+1
>
>
> I know the Ranking I used sorts ONLY on the [Ext BO] field but I am not a
> coder and do not know how to modify the format of my query to get what I need.
>
>
> Can someone please help??
>
>
> Thanks,
> --
> Chip
From: CBender on 11 May 2010 12:38
After reading my post I thought it best if I tried to clarify a few things.

If [Host DN] >= 20000 AND [All BO] >= 100 THEN [Type] = 3
If [Host DN] >= 20000 THEN [Type] = 2
If [All BO] >= 100 THEN Type = 1


The data needs to be Ranked and sorted as shown in the example below:


[Rank] [Host DN] [All BO]
[Type]
1 328,155.34 831
3
2 134,728.16 416
3
3 1,415,578.32 87
2
4 987,821.27 69
2
5 15,578.32 1,566
1
6 1,878.81 865
1
7 543.02 72
8 89.19 43







--
Chip


"CBender" wrote:

> I am trying to Rank a field based on multiple sort criteria. My query sorts
> correctly, but the Ranking assignments are not correct. Could someone please
> review my SQL query and let me know how to correct this problem?
>
> I need to sort Descending by [Type2] AS WELL AS Descending by [Ext B/O]
>
>
>
> I used the following to assign my [Type2] field data:
>
> Type2: IIf([All BO]>=100 And [Host DN]>=20000,3,(IIf([Host
> DN]>=20000,2,(IIf([All BO]>=100,1)))))
>
>
> I am currently using the following to create my Ranking:
>
> Rank: (SELECT Count([VT].[Ext BO]) FROM [tmp_tbl_Chart5a_1] AS VT WHERE
> VT.[Ext BO] > tmp_tbl_Chart5a_1.[Ext BO])+1
>
>
> I know the Ranking I used sorts ONLY on the [Ext BO] field but I am not a
> coder and do not know how to modify the format of my query to get what I need.
>
>
> Can someone please help??
>
>
> Thanks,
> --
> Chip
From: CBender on 11 May 2010 12:41
[Rank] [Host DN] [All BO] [Type]
1 328,155.34 831 3
2 134,728.16 416 3
3 1,415,578.32 87 2
4 987,821.27 69 2
5 15,578.32 1,566 1
6 1,878.81 865 1
7 543.02 72
8 89.19 43

--
Chip


"CBender" wrote:

> I am trying to Rank a field based on multiple sort criteria. My query sorts
> correctly, but the Ranking assignments are not correct. Could someone please
> review my SQL query and let me know how to correct this problem?
>
> I need to sort Descending by [Type2] AS WELL AS Descending by [Ext B/O]
>
>
>
> I used the following to assign my [Type2] field data:
>
> Type2: IIf([All BO]>=100 And [Host DN]>=20000,3,(IIf([Host
> DN]>=20000,2,(IIf([All BO]>=100,1)))))
>
>
> I am currently using the following to create my Ranking:
>
> Rank: (SELECT Count([VT].[Ext BO]) FROM [tmp_tbl_Chart5a_1] AS VT WHERE
> VT.[Ext BO] > tmp_tbl_Chart5a_1.[Ext BO])+1
>
>
> I know the Ranking I used sorts ONLY on the [Ext BO] field but I am not a
> coder and do not know how to modify the format of my query to get what I need.
>
>
> Can someone please help??
>
>
> Thanks,
> --
> Chip
From: CBender on 11 May 2010 12:43
Hopefully THIS time the chart will post correctly!!!

[Rank] [Host DN] [All BO] [Type]
1 328,155.34 831 3
2 134,728.16 416 3
3 1,415,578.32 87 2
4 987,821.27 69 2
5 15,578.32 1,566 1
6 1,878.81 865 1
7 543.02 72
8 89.19 43

--
Chip


"CBender" wrote:

> I am trying to Rank a field based on multiple sort criteria. My query sorts
> correctly, but the Ranking assignments are not correct. Could someone please
> review my SQL query and let me know how to correct this problem?
>
> I need to sort Descending by [Type2] AS WELL AS Descending by [Ext B/O]
>
>
>
> I used the following to assign my [Type2] field data:
>
> Type2: IIf([All BO]>=100 And [Host DN]>=20000,3,(IIf([Host
> DN]>=20000,2,(IIf([All BO]>=100,1)))))
>
>
> I am currently using the following to create my Ranking:
>
> Rank: (SELECT Count([VT].[Ext BO]) FROM [tmp_tbl_Chart5a_1] AS VT WHERE
> VT.[Ext BO] > tmp_tbl_Chart5a_1.[Ext BO])+1
>
>
> I know the Ranking I used sorts ONLY on the [Ext BO] field but I am not a
> coder and do not know how to modify the format of my query to get what I need.
>
>
> Can someone please help??
>
>
> Thanks,
> --
> Chip
 |  Next  |  Last
Pages: 1 2 3
Prev: How can I refresh the master form from a subform?
Next: Delete Query Help