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 07:39.

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

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

  4. #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

  5. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,535
    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-27 at 23:37.

  6. #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

  7. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,535
    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 20: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
  •