Proper Setup of Rank Tables

[TJVFree]

I’m working on setting up ranking table that should rank 3 months apps. 6 months apps and 12 month apps.

The ranks should be laid out from 1-total number of records.
For some reason it’s not do this.

Could someone please take a look at the code below or the attached database and help me understand why my formulas are not working out.

I really appreciate the time and help, I’ve been working on this all weekend

Corey

[sql]SELECT Wholesale_Group_1_export_tbl.[OMNI#], Wholesale_Group_1_export_tbl.[3MonthTotalRecords#],
(Select count(*) from Wholesale_Group_1_export_tbl as B where Wholesale_Group_1_export_tbl.[3MonthTotalRecords#] < B.[3MonthTotalRecords#]) AS 3MonthRank, Wholesale_Group_1_export_tbl.[6MonthTotalRecords#],
(Select count(*) from Wholesale_Group_1_export_tbl as B where Wholesale_Group_1_export_tbl.[6MonthTotalRecords#] < B.[6MonthTotalRecords#]) AS 6MonthRank,
Wholesale_Group_1_export_tbl.[12MonthTotalRecords#],
(Select count(*) from Wholesale_Group_1_export_tbl as B where Wholesale_Group_1_export_tbl.[12MonthTotalRecords#] < B.[12MonthTotalRecords#]) AS 12MonthRank
FROM Wholesale_Group_1_export_tbl;[/sql]

 

[Golom]

Your sub-queries don't restrict you to any particular record in the table with alias B. For example

Select count(*) from Wholesale_Group_1_export_tbl as B
 where Wholesale_Group_1_export_tbl.[3MonthTotalRecords#] < B.[3MonthTotalRecords#]) AS 3MonthRank

Has no specification for which record should be the source for [blue]B.[3MonthTotalRecords#][/blue]. Since there is no implicit ordering in SQL, you could be pulling that value from any arbitrarily selected record.

I'm not clear about exactly what result you expect but it's possible that you want to return a count where the value in the outer query is less than the MAX value for the field for a particular [OMNI#] value.

 

[TJVFree]

I jzwp22, and thank you for taking the time to help me with this issue.

I’m still learning so I really appreciate the time your spending helping me

Does his help?

OMNI# is my primary key

Rank the
3MonthTotalRecord$: is the total dollars for the past 3 months
And or
3MonthTotalRecords#: is the total apps for the past 3 months

And I’ll be doing the same with the 6, 9 and 12

I’m also looking for any recommendation.

End results
3month / 6month / 9month
4/5/ 9
7/4/2

 

[Golom]

As I read your code you are attempting to report a rank of various parameters (3Month, 6Month, 12Month) for each OMNI# and you state that, for example 3MonthTotalRecords# is the total apps for the past 3 months and that field has a value for each OMNI# record. We therefore have something like this
[tt]
OMNI# 3MonthTotalRecords#
1 25
2 41
3 34
[blue]etc.[/blue]
[/tt]
If that is the case then how does the 3MonthTotalRecords# value relate to the OMNI# on the same record? Even more to the point, does a value for 3MonthTotalRecords# for one OMNI# have meaning for any other OMNI#?

What would you expect to see as the output from your query if you had sample data like the above?