Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    May 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Select Count (*) for Ranking in MS Access 2007

    I am using (Select Count(*) from MALETABLE Where [PTS] > [MALETBL].[PTS])+1 formula to rank the records in a query.
    MALETABLE is the source query
    [PTS] is the field containing the data being ranked
    [MALETBL] is the Alias name for the field list in the query
    but the Alias for the field list in the query is being rejected; giving an error "the Access engine does not recognize '[MALETBL].[PTS]' as valid fieldname or expression.

    Any assistance I highly appreciate. It urgent please
    Last edited by Monicah; 2014-05-27 at 08:39.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,538
    Thanks
    0
    Thanked 23 Times in 23 Posts
    Why not give us the entire SQL so we can help

  3. #3
    New Lounger
    Join Date
    May 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi. Here is the full Sql code
    PARAMETERS [ENTER SEASON] Long;
    SELECT (Select Count(*) from MALETABLE Where [PTS] > [MALETBL].[PTS])+1 AS POS, MALETBL.SEASON, MALETBL.P, MALETBL.[SCHOOL ID], MALETBL.[SCHOOL NAME], MALETBL.D, MALETBL.L, MALETBL.W, MALETBL.GS, MALETBL.GA, MALETBL.GD, MALETBL.PTS, MALETBL.GENDER
    FROM MALETABLE AS MALETBL
    WHERE (((MALETBL.SEASON)=[ENTER SEASON]));

    SEASON is the year the record was entered

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,538
    Thanks
    0
    Thanked 23 Times in 23 Posts
    There are posts in here that show how that is done, I don't remember where they are or how it's done, someone else will know. I will keep looking
    I have tried this in a database I have and it seems to work, change the table name and field name and see if it works:
    SELECT (SELECT Count(*) +1 FROM [Zulu Trades] AS A WHERE A.id_AN < B.id_AN) AS RowNumber
    FROM [zulu trades] AS B;
    Last edited by patt; 2014-05-28 at 00:37.

  5. #5
    New Lounger
    Join Date
    May 2014
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi. Thanks for help. I tried the code using your tip, but didn't work. so I thought my Ms Excel has a problem. I gave it a rest. Now I know what's wrong. I've combine my query with a crosstab, I think its the one causing the error.
    Thanks again

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,538
    Thanks
    0
    Thanked 23 Times in 23 Posts
    My SQL above works in a database I tried it in.
    You say you have combined a crosstab query within your query, would you send a cut down zipped version of your database and let people see what you are trying to do.
    BTW I doubt if you can use a crosstab query in that query tho
    Last edited by patt; 2014-06-23 at 21:59.

Posting Permissions

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