472,328 Members | 1,698 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,328 software developers and data experts.

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, 63 views)
Jan 10 '11 #1
4 1067
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,832 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

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

Similar topics

0
by: Alexis | last post by:
Is there any option of formatting the columns of tables so that for example only 5 characters of each column is displayed?(also is there any way to...
1
by: MB | last post by:
I need to develop a Cold Fusion application using SQL tables, I am not sure how to setup my tables or that this is the optimal way of setting my...
1
by: R. de Laat | last post by:
Hi there, I recently started writing perl and i like it more and more. Syntax is something you can easily learn from a book and then just do...
3
by: Random Person | last post by:
Does anyone know how to use VBA to relink tables between two MS Access databases? We have two databases, one with VBA code and the other with data...
4
by: Paul .V. | last post by:
I need to setup two tables with default information. First off I need a company table. Quite simple. Name, Address, phone, tax number and so on....
3
by: BPDudeMan | last post by:
Hi There, I've got one table that is constantly being added to (every few seconds). I've got a bunch of users that need to report from this...
2
by: orenlevy1 | last post by:
Hi Everyone. I have a problem that I could not figure out what to do with it. I have a couple of tables and views. All have the same unique ID....
1
by: cerise | last post by:
I'm using a Linked list stack with objects. I figured out the reason for my earlier problem (where I couldn't access "rank" and "suit" from one of...
2
by: mo/-/sin | last post by:
i made a table in sql server 2005 and there is column name dateofjoining... but whenever i insert a value in this column it shows some other...
1
by: Justin | last post by:
We have a table that is growing quickly and would like to partition it by date. However, the table does not have a date field. With another...
0
by: tammygombez | last post by:
Hey everyone! I've been researching gaming laptops lately, and I must say, they can get pretty expensive. However, I've come across some great...
0
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
0
better678
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
0
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
0
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
by: CD Tom | last post by:
This only shows up in access runtime. When a user select a report from my report menu when they close the report they get a menu I've called Add-ins...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.