Results 1 to 4 of 4
  1. #1
    kiranmahale is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    3

    How to Use Rank function in MSACCESS

    I traying to use Oracle Rank function in MSACCESS. How do I do that? Here is table and output I am looking for
    Table: TaxType
    Tax_no Tax_Name Start_Date Tax_Percent
    ----------------------------------------------------------------------
    1 VAT 1/1/2008 2.3


    2 VAT 1/1/2009 2.5
    3 VAT 1/1/2010 2.6
    4 REW 2/1/2008 1.6
    5 REW 2/3/2009 4.3
    6 OTH 3/1/2008 5.6
    7 TGH 11/1/2009 6.7
    If I pass a Date 10/1/2009 I need below result(ie maxdate of each Tax_Name with percentage)
    1 VAT 1/1/2009 2.5
    5 REW 2/3/2009 4.3
    6 OTH 3/1/2008 5.6

    I used to do this in oracle using RANK function. I do not know how to do this in MSACCESS
    SELECT Tax_ID, Tax_Name, Start_Date, Tax_Percent,
    RANK() OVER (PARTITION BY Tax_Name ORDER BY Start_Date desc) as Date_rank
    FROM TaxType where start_date<=to_date(10/01/2010, 'mm/dd/yyyy')

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,849
    I don't have AC 2010, but I do not believe a Rank function similar to the Oracle function exists in MS Access.

  3. #3
    kiranmahale is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    3
    Can you pls tell me is there any alternative SQL option to get the result.

  4. #4
    kiranmahale is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Dec 2011
    Posts
    3
    I got the query running
    select a.*
    from taxtype a inner join
    (select b.tax_name, max(b.start_date) as start_date from taxtype b
    where b.start_date < #10/10/2010#
    group by b.tax_name) c on a.tax_name=c.tax_name and a.start_date=c.start_date

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Learn MSAccess by playing MSAccess Jeopardy
    By pkstormy in forum Sample Databases
    Replies: 4
    Last Post: 11-17-2016, 07:27 AM
  2. please need help RANK
    By Andyjones in forum Queries
    Replies: 8
    Last Post: 09-10-2011, 12:12 AM
  3. Rank Function
    By Dongola in forum Programming
    Replies: 1
    Last Post: 05-07-2011, 06:05 PM
  4. MSACCESS Query
    By saa18 in forum Access
    Replies: 0
    Last Post: 11-14-2008, 05:12 AM
  5. Seeking to pruduce rank by year
    By JLR in forum Queries
    Replies: 0
    Last Post: 01-21-2008, 06:10 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums