SQL to ACCESS

[Guest]

I inherited some SQL that needs to be run. What is the best way to convert
this to be used in access. There is a lot of code so I will just include the
first few lines. Is it even possible to recreate this using a query?

CREATE TABLE PPO_RNKDEPSDS_PHYS AS

SELECT *
FROM (SELECT RANK() OVER (PARTITION BY PRXH_RPTG_GRP_NM,PRXH_RPTG_SUBGP_NM,
PRXH_RPTG_SPCGP_NM, PROV_NM
ORDER BY Round(DECODE(SIGN(Sum(b.PPTE_PTE_CMPBY_PCT)-1), -1,
Sum(b.PPTE_TOTDR_CVC_AMT),
(Sum(b.PPTE_TOTDR_CVC_AMT)/Sum(b.PPTE_PTE_CMPBY_PCT))),2) Desc) As
Rank_TACC,
RANK() OVER (PARTITION BY PRXH_RPTG_GRP_NM,PRXH_RPTG_SUBGP_NM,
PRXH_RPTG_SPCGP_NM, PROV_NM
ORDER BY Sum(b.PPTE_PTE_CMPBY_PCT) DESC) As Rank_Vol,
Round(DECODE(SIGN(Sum(b.PPTE_PTE_CMPBY_PCT)-1), -1,
(Sum(b.PPTE_TOTDR_CVC_AMT)-Sum(b.PPTE_PSCPTN_CHG_AMT)),

FROM PRVRPTG.PROV a,
PRVRPTG.PPTE_TEPSD b

 

[Guest]

My guess is that you cannot create this into a SQL-statement that can be run
under Access directly. In this statement specific SQL 2005 functions are
being called (Rank Over Partition by). Is Access used only as a front end or
are you converting this completely to Access because in that case I don't
think it can be done and you'd have to rewrite the statement. But let's see
what other people think of this...