473,544 Members | 1,594 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Proper Setup of Rank Tables

72 New Member
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


[sql]SELECT Wholesale_Group _1_export_tbl.[OMNI#], Wholesale_Group _1_export_tbl.[3MonthTotalReco rds#],
(Select count(*) from Wholesale_Group _1_export_tbl as B where Wholesale_Group _1_export_tbl.[3MonthTotalReco rds#] < B.[3MonthTotalReco rds#]) AS 3MonthRank, Wholesale_Group _1_export_tbl.[6MonthTotalReco rds#],
(Select count(*) from Wholesale_Group _1_export_tbl as B where Wholesale_Group _1_export_tbl.[6MonthTotalReco rds#] < B.[6MonthTotalReco rds#]) AS 6MonthRank,
Wholesale_Group _1_export_tbl.[12MonthTotalRec ords#],
(Select count(*) from Wholesale_Group _1_export_tbl as B where Wholesale_Group _1_export_tbl.[12MonthTotalRec ords#] < B.[12MonthTotalRec ords#]) 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 1115
12,516 Recognized Expert Moderator MVP
Your table alias needs to be in the outer query so that the subquery can reference the outer table.
Jan 10 '11 #2
8,834 Recognized Expert Expert
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
Dave Smith
72 New Member
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
12,516 Recognized Expert Moderator MVP
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
Jan 10 '11 #5

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

Similar topics

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 set no wrapping) That is in order to get a proper output of tables in a win98 DOS prompt. I know that Oracle SQL gives you such options. Thanks in advance, Alexis
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 tables for the application that I am trying top automate. I've first created 2 tables as follows: SOSC ----- 1 Record ID (Primary key) 2 CMP_TIME_ID (Foreign key)
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 it. You will get better as you go. But i seem to get stuck on the best way to setup an application. I've read about the perl modules and how to write your own. Does one write a lot of modules and...
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 tables. The tables are referenced by linked tables in the database where the code resides. If we move the database with the data tables to a new directory, the links are no longer valid. I...
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. So I have setup that table with the fiels corresponding to the above. Question #1 When I print a report I need to look up one value, the tax number. I assume I use the dLookup function but...
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 table. What's the best way to setup the tables? Create a second table and updates it with tblLive data every let's say 15 minutes and then report off of that? Is there a way to synch tables that...
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. When a user try to do a search on our web site we want to try to look in multiple tables and views, Rank the results and aggregate the results back (remove any duplicates). Part of the search is...
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 the objects in my stack), and it was because my object didn't have the attributes "rank" and "suit". Turns out my earlier cards were practically just strings. Can anyone help me create a whole deck of...
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 value........ suppose i insert 2005-09-25 but it shows 1905-05-26.... why it is happening..............
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 table, we are able to create a link to bring in the date. Is it possible to create a table that is partitioned by date from a field in a different table? Hope that is clear. -JB
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Duprť who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.