How to Use Rank function in MSACCESS

[kiranmahale]

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')

[orange]

I don't have AC 2010, but I do not believe a Rank function similar to the Oracle function exists in MS Access.

[kiranmahale]

Can you pls tell me is there any alternative SQL option to get the result.

[kiranmahale]

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