something like this? Expr1: IIf([3MonthRank]="*5089*",0,[3MonthRank])

[TJVFree]

Would someone please take a look at my database and help me find a way to change the “5089” to just be the last number in my ranking.

So in 3MonthRank all the “5089’s” should be 692
And in 6MonthRank all the “5089’s” should be 750

I’m hoping there is a easy way to do this.

The attached will show you exactly what I’m asking

I really appreciate you taking the time to help.

Below is the code if that helps.

[sql](Select count(*) from Wholesale_Group_1_export_tbl as B where Wholesale_Group_1_export_tbl.[3MonthTotalRecord$] < B.[3MonthTotalRecord$]+1) AS 3MonthRank,
Wholesale_Group_1_export_tbl.[3MonthTotalRecord$],
Wholesale_Group_1_export_tbl.[3MonthTotalRecords#],

(Select count(*) from Wholesale_Group_1_export_tbl as B where Wholesale_Group_1_export_tbl.[6MonthTotalRecords$] < B.[6MonthTotalRecords$]+1) AS 6MonthRank,
Wholesale_Group_1_export_tbl.[6MonthTotalRecords$],
Wholesale_Group_1_export_tbl.[6MonthTotalRecords#],

(Select count(*) from Wholesale_Group_1_export_tbl as B where Wholesale_Group_1_export_tbl.[9MonthTotalRecords$] < B.[9MonthTotalRecords$]+1) AS 9MonthRank, Wholesale_Group_1_export_tbl.[9MonthTotalRecords$], Wholesale_Group_1_export_tbl.[9MonthTotalRecords#], (Select count(*) from Wholesale_Group_1_export_tbl as B where Wholesale_Group_1_export_tbl.[12MonthTotalRecord$] < B.[12MonthTotalRecord$]+1) AS 12MonthRank, Wholesale_Group_1_export_tbl.[12MonthTotalRecord$], Wholesale_Group_1_export_tbl.[12MonthTotalRecords#] INTO RankTable_tbl

FROM Wholesale_Group_1_export_tbl
ORDER BY Wholesale_Group_1_export_tbl.[3MonthTotalRecord$] DESC , Wholesale_Group_1_export_tbl.[6MonthTotalRecords$] DESC , Wholesale_Group_1_export_tbl.[9MonthTotalRecords$] DESC;[/sql]

 

[Andrzejek]

That doesn't make any sense.

Are you talking about some simple Update statements like:

Update MyTable
Set SomeField = 692
Where Rank = '3MonthRank'
And SomeField = 5089

And do the same for 6MonthRank ???

Have fun.

---- Andy