Proper Setup of Rank Tables

[TBC]

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]

 

[jzwp22]

You have several problems with your database.

First and most important, your table is inconsistent with a normalized table structure. It looks like you are trying to use an Access table as if it were a spreadsheet; it is not. In order to set up your database properly, you will have to understand normalization. This site gives a basic overview.

You current table contains a bunch of fields related to monthly data. Monthly data should be records in a table, not fields. Additionally, in a properly structured database, you would not store totals (i.e. your 3-month, 6-month and 12-month total fields), but rather, calculate them on the fly when you need them

Secondly, your fields contain numeric data, but you have the datatype as text.

 

[TBC]

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

I’m still learning so I really appreciate the help with normalizing. Once I’m able to complete this report I’m going to go back and work on normalizing my database, is there a chance in the meantime we are able to make the ranking work like the below?

What I’m trying to do is either:

Rank the
3MonthTotalRecord$
And or
3MonthTotalRecords#

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

I’m also looking for any recommendation.

 

[jzwp22]

I'm not sure what you are really trying to do

If I look at OMNI# 0102 and add up the number of application counts for the year, I get 8. But the Total Apps YTD field lists 61. From where did the 61 come?

Can you provide an example of what you expect the data to look like for a particular subgroup of records?

You might have to first add up each value for the months of the period of interest and then do the ranking.

It would be a more efficient use of your time to normalize first and then do the queries because once you normalize, any previous queries would be worthless.

BTW, you still have to convert the text fields to numbers otherwise nothing will add right anyway.