Help Converting Oracle PL/SQL Query into Access

[RThomas321]

Can anyone help with converting this PL/SQL query for Access 2010?
Particularly, the partition functionality.

select Table_A.number0,
Table_B.ST_CD,


Table_C.ID_CD AS SPID,
to_char(Table_D.COLLECT_DT, 'MON-YY') as Mprd,
count(Table_D.D_number)
over (partition by --IDENTIFICATION_CD,
Table_A.number0,
to_char(Table_D.COLLECT_DT, 'MON-YY'),
substr(Table_C.ID_CD,6,5)
) as SampleCt,
avg(CONCENTRATION)
over (partition by --IDENTIFICATION_CD,
Table_A.number0,
to_char(Table_D.COLLECT_DT, 'MON-YY'),
substr(Table_C.ID_CD,6,5)) as SampAvg,
Table_D.COLLECT_DT AS SAMPLEDATE,
TABLE_E.CONCENTRATION

FROM Table_A
inner join Table_B
on TABLE_B.SYS_NUMBER = Table_A.SYS_NUMBER
inner join TABLE_C
on TABLE_C.B_NUMBER = TABLE_B.B_NUMBER
inner join TABLE_D
on TABLE_D.C_NUMBER = TABLE_C.C_NUMBER
inner join TABLE_E
on TABLE_E.D_NUMBER = TABLE_D.D_NUMBER
inner join TABLE_F
on TABLE_E.F_NUMBER = TABLE_F.F_NUMBER
where Table_F.CODE in ('1009', '1011');

[June7]

I think the closest you will get in Access is an aggregate GROUP BY (Totals) query or a nested subquery or a report using its Grouping & Sorting functionality with aggregate calcs in header/footer sections. The report will allow display of detail records and summary calcs.

http://stackoverflow.com/questions/2...y-and-group-by

[orange]

I don't think this is a simple conversion, nor some application of aggregate functions.
see this for more re Oracle analytic functions.

I don't recall anyone trying to develop equivalent functions with vba/Access. I'm not familiar with SQL server, but I did find a link to show those functions(or similar functionality) exist in SQL server.

Via Google I found this that may be of some help.

Good luck

[June7]

If ranking is the goal, review http://allenbrowne.com/ranking.html

Exactly what is the output you want?

[orange]

It's the Over and Partition By that isn't available in Access.
I'm sure someone has tried to create functions to do this in Access/vba, but I haven't been able to find any.

Here are a couple of links to threads with similar issue and "solution" (more gooogling!)

http://www.mrexcel.com/forum/microso...partition.html

http://www.access-programmers.co.uk/...d.php?t=206471

[RThomas321]

It's the Over and Partition By that isn't available in Access.
I'm sure someone has tried to create functions to do this in Access/vba, but I haven't been able to find any.

Here are a couple of links to threads with similar issue and "solution" (more gooogling!)

http://www.mrexcel.com/forum/microso...partition.html

http://www.access-programmers.co.uk/...d.php?t=206471

Thanks, I'll check those out.