Help with making a data partition in access

[adgjqetuo]

Hello,

If I was using Oracle I would typically use a "ROW_NUMBER () OVER PARTITION BY" function, however it doesn't appear access supports that.

I created the below query which works great, except it doesn't partition by the empl_id field.

I get that is the function of "Top 1", but I really want the Top 1 for each unique empl_id ordered by the resp_date desc.

Can someone help me? I've been searching for a solution all morning.

select u.* from( 
select Top 1 v.* from ( 
select w.* from(
select Top 1 x.* from (
select y.* from (
select Top 1 z.* from(

select distinct e.empl_last_name & ', ' & e.empl_first_name &  ' (' & ex.empl_id & ')' & ' - ' & j.title, e.empl_last_name, 
                   e.empl_first_name, 
                   ex.empl_id, 
                   j.title, 
                   jx.ref_id, 
                   ex.resp_date as exresp, 
                   ex.status as exstatus, 
                   ax.resp_date as axresp, 
                   ax.status as axstatus, 
                   jx.status as jxstatus, 
                   jx.resp_date as jxresp 

from employee as e, 
         employee_ref as ex, 
         account as a, 
         account_ref as ax, 
         job_desc as j, 
         job_desc_ref as jx 


where e.ref_id=ex.ref_id 
and     a.ref_id=ax.ref_id 
and     jx.ref_id=j.ref_id 
and     ax.empl_id=ex.empl_id 
and     jx.empl_id=ex.empl_id
and     ((a.account_id)=left(right((forms!client_view.client_combo.value),5),3)) 
and      jx.ref_id in (14,15)) z 

order by z.exresp desc) y 
where y.exstatus = 'A') x 
order by x.axresp  desc) w 
where w.axstatus = 'A') v 
order by v.jxresp  desc) u 
where u.jxstatus = 'A' 
order by u.ref_id, u.empl_last_name asc;

 

[jdraw]

There are no equivalent functions to those in Oracle.

I suggest you zip a copy of the database you're working with and post it here along with some verbiage and samples of exactly what you want.

I haven't used Oracle for over 4 years, but I know those functions aren't in Access.

When you have less than 10 posts you can still send a zip.

 

[adgjqetuo]

So is there another solution to make my query work?

And to clarify, I need this to work in Access 2007, not Oracle.

My current query above works as-is in Access, I just need it to partition by each empl_id.

 

[CJ_London]

If I understand you correctly you can use a subquery to achieve the same thing -

following on in your criteria

and      jx.ref_id in (14,15)) z 
and      resp_date=(SELECT Min(resp_date) FROM Employees AS T WHERE empl_id=[COLOR=red]Employees[/COLOR].empl_id)

or an alternative way

and      jx.ref_id in (14,15)) z 
and      resp_date=(SELECT TOP 1 resp_date FROM Employees AS T WHERE empl_id=[COLOR=red]Employees[/COLOR].empl_id ORDER BY resp_date desc)

Without testing you may need to change the criteria in red to work off your alias

 

[adgjqetuo]

Hi - thank you!

I think you may be onto something, but i'm getting a syntax error when trying to implement - maybe i'm not correctly joining in the new "T" sub query?

 

[jdraw]

I realize you're working in Access.
Can you post a dumbed down version of your database (nothing confidential and only enough records for testing).

 

[adgjqetuo]

I think I finally got it working with CJ_London's suggestion! I have to conduct through test later on this evening though.

select u.* from( 
select v.* from ( 
select w.* from(
select x.* from (
select y.* from (

select z.* from(

select distinct e.empl_last_name & ', ' & e.empl_first_name &  ' (' & ex.empl_id & ')' & ' - ' & j.title, e.empl_last_name, 
                        e.empl_first_name, 
                        ex.empl_id, 
                        j.title, 
                        jx.ref_id, 
                       ex.resp_date as exresp, 
                       ex.status as exstatus, 
                       ax.resp_date as axresp, 
                       ax.status as axstatus, 
                       jx.status as jxstatus, 
                       jx.resp_date as jxresp 

from employee as e, 
         employee_ref as ex, 
         account as a, 
         account_ref as ax, 
         job_desc as j, 
         job_desc_ref as jx 


where e.ref_id=ex.ref_id 
and     a.ref_id=ax.ref_id 
and     jx.ref_id=j.ref_id 
and     ax.empl_id=ex.empl_id 
and     jx.empl_id=ex.empl_id
and     ((a.account_id)=left(right((forms!client_view.client_combo.value),5),3)) 
and      jx.ref_id in (7,8)) z 

order by z.exresp desc) y 
where y.exstatus = 'A'
and y.exresp = (SELECT Max(T.resp_date) FROM Employee_ref T WHERE y.empl_id=t.empl_id)) x 

order by x.axresp  desc) w 
where w.axstatus = 'A'
and w.axresp = (SELECT Max(T.resp_date) FROM Account_ref T WHERE w.empl_id=t.empl_id)) v 

order by v.jxresp  desc) u 
where u.jxstatus = 'A' 
and u.jxresp = (SELECT Max(T.resp_date) FROM Job_Desc_ref T WHERE u.empl_id=t.empl_id)


order by u.ref_id, u.empl_last_name asc;

I'll let you know by Monday if it's working 100% or not.