Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Dec 2010
    Location
    Minnesota
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 Attached Files

  2. #2
    Star Lounger
    Join Date
    Sep 2002
    Location
    Hastings, Sussex, England
    Posts
    67
    Thanks
    0
    Thanked 1 Time in 1 Post
    Corey,

    sorry, I'm having trouble getting my head around the result you are aiming for. I can offer a quick suggestion, though.

    Several of your fields are actually totals subqueries, e.g. 3MonthRank: (Select count(*) from Wholesale_Group_1_export_tbl as B where Wholesale_Group_1_export_tbl.[3MonthTotalRecords#] < B.[3MonthTotalRecords#]). You do not have a primary key on the table, but I assume that OMNI# holds unique values.

    Why not have this as a separate query grouped by OMNI#, and pull in the result? You could do the same for the other subqueries. It is easier to debug when steps are broken down.

    This might be a first step to a solution.

    Regards,

    Jules

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by JulesG View Post
    Several of your fields are actually totals subqueries, e.g. 3MonthRank: (Select count(*) from Wholesale_Group_1_export_tbl as B where Wholesale_Group_1_export_tbl.[3MonthTotalRecords#] < B.[3MonthTotalRecords#]). You do not have a primary key on the table, but I assume that OMNI# holds unique values.

    Why not have this as a separate query grouped by OMNI#, and pull in the result? You could do the same for the other subqueries. It is easier to debug when steps are broken down.
    To do as you suggest would require (potentially) hundreds or thousands of subqueries, as the sql above is counting the number of records where 3MonthTotalRecords# is less than 3MonthTotalRecords# in the current record. So the subquery is different for each row.
    Regards
    John



  4. #4
    Star Lounger
    Join Date
    Sep 2002
    Location
    Hastings, Sussex, England
    Posts
    67
    Thanks
    0
    Thanked 1 Time in 1 Post
    I stand corrected!

    Jules

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •