INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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

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

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.

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close