Need help with a query (Access 97)

Topic

New Reply

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

Reply | Quote

Replies

What about using a calculated field:

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

Tom

Reply | Quote

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*”))

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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*”))

Reply | Quote