access formula approach (RD workorders within 15 days of the closing of the last PM workorder)

[tav1035]

I starting a data pull using access and I am looking for ideas before I get too involved.

I'm looking to know if there is a way to find RD workorder's (reportdate) within 15 days after a PM workorder's ( actfinish) date on an asset using access and Oracle.

Fields-
VIEW_WORKORDER.WONUM
VIEW_WORKORDER.DESCRIPTION
VIEW_WORKORDER.ASSETNUM
VIEW_WORKORDER.WORKTYPE
VIEW_WORKORDER.REPORTDATE
VIEW_WORKORDER.ACTFINISH

Data-
1001 | Check and Change Filters | AHU01 | PM | 1/1/2016 | 1/1/2016
1005 | Low Pressure | AHU01 | RD | 1/14/2016 | 1/14/2016
1008 | Clogged Filter | AHU01 | RD | 1/16/2016 | 1/16/2016

In the case above, I would want to see the PM workorder# 1001 and the RD workorder# 1005 only.

I was thinking an expression would work but don't know the syntax-
Expr1: [VIEW_WORKORDER]![WORKTYPE]='PM' And [VIEW_WORKORDER]![ACTFINISH]< [VIEW_WORKORDER]![WORKTYPE] ='RD' [VIEW_WORKORDER]![REPORTDATE] +15

or maybe a different approach by creating 2 make tables, one for each worktype. Then bringing them together somehow.

This is a formula that I used in crystal-
if
{workorder.assetnum} = previous({workorder.assetnum}) and
previous({workorder.worktype}) = "PM" and
({workorder.worktype}) = "RD" and
datediff("d",previous({WORKORDER.REPORTDATE}),{WORKORDER.REPORTDATE})<5
or

{workorder.assetnum} = next({workorder.assetnum}) and
next({workorder.worktype}) = "RD" and
({workorder.worktype}) = "PM" and
datediff("d",{WORKORDER.REPORTDATE},next({WORKORDER.REPORTDATE}))<5
then
"Keep"


Any ideas of what that may look like would be appreciated.
TAV

 

[lameid]

It seems like a little criteria would to it. If using an Access Query I would say a Where clause like below would work.

Otherwise you really should be asking in an Oracle forum for Oracle SQL (pl/SQL). I know T-SQL for sql server would look the same but without the double quotes. I just haven't done enough with Oracle to happen to know the answer.

Where Dateadd("d",15, ReportDate) <= Actfinish