Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Dec 2010
    Location
    Minnesota
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm receiving a syntax error "See Below" when I'm trying to run a Rank query. For those of you that want to see the database and how I'm running this sql in it I have attached it. I have also attached a picture of the error message I'm receiving.

    To explain the end result I'm trying to get is to:
    Rank the number of Apps that are within a first three months between #01/01/10# and #03/30/10#
    I will do this for the 6Month_App_Count, 9Month_App_Count, and 12Month_App_Count.

    I will also be looking at getting a Monthly_App_Count_Ranking "Jan" "Feb" and so on.

    If you open DB look at the qryWeightedRank query

    any help would be great, Iím slowly learning and all your help and advice is helping

    Syntax error. In query expressioní(Select count(*)from GP1_Maaster_Table_Count_Apps as B where GP1_Master_Table_Count_Apps.3Month_App_Count >B.3Month_App_Count)

    ([sql]SELECT
    [GP1_Master Table_Count_Apps].OMNI_Number,
    [GP1_Master Table_Count_Apps].[3Month_App_Count],
    (Select count(*) from GP1_Master Table_Count_Apps as B where GP1_Master Table_Count_Apps.3Month_App_Count > B.3Month_App_Count) AS Rank,
    (Select count(*) from GP1_Master Table_Count_Apps) AS TotalRecords,
    [Rank]/([TotalRecords]-1)*5 AS 0to5Rank,
    CInt([0to5Rank]*10)/10 AS Star_Rating,
    (Select sum(3Month_App_Count)from qry3Month_App_Counts) AS GrandTotal,
    (select min(3Month_App_Count) from qry3Month_App_Counts) AS MinTotal, (select max(3Month_App_Count) from qry3Month_App_Counts) AS MaxTotal,
    5*(([3Month_App_Count]-[minTotal])/([MaxTotal]-[MinTotal])) AS WeightedRank
    FROM [GP1_Master Table_Count_Apps];([/sql]
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Quote Originally Posted by Corey Vlasak View Post
    I'm receiving a syntax error "See Below" when I'm trying to run a Rank query. For those of you that want to see the database and how I'm running this sql in it I have attached it. I have also attached a picture of the error message I'm receiving.

    To explain the end result I'm trying to get is to:
    Rank the number of Apps that are within a first three months between #01/01/10# and #03/30/10#
    I will do this for the 6Month_App_Count, 9Month_App_Count, and 12Month_App_Count.

    I will also be looking at getting a Monthly_App_Count_Ranking "Jan" "Feb" and so on.

    If you open DB look at the qryWeightedRank query

    any help would be great, Iím slowly learning and all your help and advice is helping

    Syntax error. In query expressioní(Select count(*)from GP1_Maaster_Table_Count_Apps as B where GP1_Master_Table_Count_Apps.3Month_App_Count >B.3Month_App_Count)

    ([sql]SELECT
    [GP1_Master Table_Count_Apps].OMNI_Number,
    [GP1_Master Table_Count_Apps].[3Month_App_Count],
    (Select count(*) from GP1_Master Table_Count_Apps as B where GP1_Master Table_Count_Apps.3Month_App_Count > B.3Month_App_Count) AS Rank,
    (Select count(*) from GP1_Master Table_Count_Apps) AS TotalRecords,
    [Rank]/([TotalRecords]-1)*5 AS 0to5Rank,
    CInt([0to5Rank]*10)/10 AS Star_Rating,
    (Select sum(3Month_App_Count)from qry3Month_App_Counts) AS GrandTotal,
    (select min(3Month_App_Count) from qry3Month_App_Counts) AS MinTotal, (select max(3Month_App_Count) from qry3Month_App_Counts) AS MaxTotal,
    5*(([3Month_App_Count]-[minTotal])/([MaxTotal]-[MinTotal])) AS WeightedRank
    FROM [GP1_Master Table_Count_Apps];([/sql]
    Trying to run query qryWeightedRank results in an error "cannot find table or query qryCustomerTotals1". So its impossible to run this query.

    That syntax error is probably due to a space in the table name or a misspelt table name.

    I have included the Syntax error here for perusal as the one above is not clear enough.
    Attached Images Attached Images

Posting Permissions

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