Proper Setup of Rank Tables

[Dave Smith]

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]

[Rabbit]

Your table alias needs to be in the outer query so that the subquery can reference the outer table.

[ADezii]

Aside from what Rabbit has stated, shouldn't the following Fields be NUMERIC? They are currently defined as TEXT:

[3MonthTotalRecords#]
[6MonthTotalRecords#]
[9MonthTotalRecords#]
[12MonthTotalRecords#]

[Dave Smith]

Can yuo help me understand how I would set the outer query so that the subquery can reference the outer table

[Rabbit]

SELECT UniqueID,
(SELECT Description FROM Table1 WHERE x.UniqueID = Table1.UniqueID) AS Desc
FROM Table1 AS x