469,091 Members | 1,230 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,091 developers. It's quick & easy.

Proper Setup of Rank Tables

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]
Attached Files
File Type: zip NewDB.zip (137.9 KB, 45 views)
Jan 10 '11 #1
4 994
Rabbit
12,516 Expert Mod 8TB
Your table alias needs to be in the outer query so that the subquery can reference the outer table.
Jan 10 '11 #2
ADezii
8,800 Expert 8TB
Aside from what Rabbit has stated, shouldn't the following Fields be NUMERIC? They are currently defined as TEXT:
Expand|Select|Wrap|Line Numbers
  1. [3MonthTotalRecords#]
  2. [6MonthTotalRecords#]
  3. [9MonthTotalRecords#]
  4. [12MonthTotalRecords#]
Jan 10 '11 #3
Can yuo help me understand how I would set the outer query so that the subquery can reference the outer table
Jan 10 '11 #4
Rabbit
12,516 Expert Mod 8TB
Expand|Select|Wrap|Line Numbers
  1. SELECT UniqueID,
  2. (SELECT Description FROM Table1 WHERE x.UniqueID = Table1.UniqueID) AS Desc
  3. FROM Table1 AS x
  4.  
Jan 10 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Alexis | last post: by
1 post views Thread by R. de Laat | last post: by
3 posts views Thread by Random Person | last post: by
4 posts views Thread by Paul .V. | last post: by
3 posts views Thread by BPDudeMan | last post: by
1 post views Thread by Justin | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.