Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need help with a query (Access 97)

    This is a sample of a db that is used to track operator/machine utilization and productivity. Aquired new machines (APS) in March that now take 2 people to operate. From March until now, only one entry is made for each APS machine per shift/day. So we really aren't tracking the productivity of the 2nd person that it takes to run an APS machine.

    Is there a way to create a query that can take any entries with an EndDate >= 06/01/2004, where the MachineName is like *APS*, and take the RunTime and divide that number by 2 (because we will now have an entry for each associate). But at the same time, perform no calculation for entries prior to 06/01/04 and no calculations on machinenames that are not APS machines.

    Confusing, but I hope that you can help.

    Thanks,

    Marie
    Attached Files Attached Files

  2. #2
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need help with a query (Access 97)

    What about using a calculated field:

    IIf([EndDate}>=#06/01/2004# and Left([MachineName],3)="APS",[RunTime]/2,[RunTime]

    Tom

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need help with a query (Access 97)

    Tom's expression (with a bracket corrected and a parenthesis added) should do what you want:

    IIf([EndDate]>=#6/1/2004# And Left([MachineName],3)="APS",[RunTime]/2,[RunTime])

    as should this one, that avoids IIf:

    [Runtime]/(1-([EndDate]>=#6/1/2004# And [MachineName] Like "*APS*"))

  4. #4
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need help with a query (Access 97)

    This worked great! As always, thanks so much for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •