Query and text box help on formula

[Guest]

Hello, I have a query that takes DT REGULAR and / it by EMPLOYEE TIME and
gives me a percent. I found out that its conflicting with other numbers and
need to fix it by having it have an if statment or something. it needs to do
this [DT REGULAR]/[EMPLOYEE TIME] then here is where im lost. If I have
somethig in DT SETUP or DT MEETINGS or DT MAINTENANCE I need to subtract the
numbers from those three things from EMPLOYEE TIME. I also have this "=[DT
REGULAR]/[EMPLOYEE TIME]" in a text box on a form that just shows the percent
and means nothing and I would need to change that formula as well to do the
same thing. here is what I have in the field of my query "Delay Percent:
Sum([DT REGULAR])/Sum([EMPLOYEE TIME])" and thats what what I have so far...
Any help would be greatly appreciated!

Thanks,
Chad

 

[John W. Vinson]

Hello, I have a query that takes DT REGULAR and / it by EMPLOYEE TIME and
gives me a percent. I found out that its conflicting with other numbers and
need to fix it by having it have an if statment or something. it needs to do
this [DT REGULAR]/[EMPLOYEE TIME] then here is where im lost. If I have
somethig in DT SETUP or DT MEETINGS or DT MAINTENANCE I need to subtract the
numbers from those three things from EMPLOYEE TIME. I also have this "=[DT
REGULAR]/[EMPLOYEE TIME]" in a text box on a form that just shows the percent
and means nothing and I would need to change that formula as well to do the
same thing. here is what I have in the field of my query "Delay Percent:
Sum([DT REGULAR])/Sum([EMPLOYEE TIME])" and thats what what I have so far...
Any help would be greatly appreciated!

Thanks,
Chad

I'm not at all sure I understand - you're perhaps assuming we know more about
your business rules and fieldnames than we do! But... try

Delay Percent: Sum([DT REGULAR]) / (Sum([EMPLOYEE TIME]) - Sum([DT SETUP]) -
Sum([DT MEETINGS]) - Sum([DT MAINTENANCE]))

I cannot figure out what you mean by doing the same calculation in a textbox
and in a calculated field, or what you are referring to by "means nothing"...

John W. Vinson [MVP]

 

[John Spencer]

Try the following

======= All this should be on one line =========

Sum([DT Regular)/Sum([Employee Time]-Nz([DT SetUP],0)-
Nz([DT Meetings])-NZ([DT Maintenance],0))

===== All the above should be on one line ======

I used the NZ (Field, 0) call in case your fields could contain blanks
(nulls). If any field were null, then the math would return blank. Nz
returns the first argument (the field) unless the first argument is
null. If the first argument is null, the NZ returns the value of the
second argument (zero in the example)


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Guest]

Ok I explained it wrong im sorry.. I need to have in this order DT SETUP,DT
MEETINGS,DT MAINTENANCE be subtracted from EMPLOYEE TIME first. then the
number I get that would be my new employee time number and it would get / DT
REGULAR but like this [DT REGULAR]/[DT EMPLOYEE TIME]

Thanks,

Try the following

======= All this should be on one line =========

Sum([DT Regular)/Sum([Employee Time]-Nz([DT SetUP],0)-
Nz([DT Meetings])-NZ([DT Maintenance],0))

===== All the above should be on one line ======

I used the NZ (Field, 0) call in case your fields could contain blanks
(nulls). If any field were null, then the math would return blank. Nz
returns the first argument (the field) unless the first argument is
null. If the first argument is null, the NZ returns the value of the
second argument (zero in the example)


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hello, I have a query that takes DT REGULAR and / it by EMPLOYEE TIME and
gives me a percent. I found out that its conflicting with other numbers and
need to fix it by having it have an if statment or something. it needs to do
this [DT REGULAR]/[EMPLOYEE TIME] then here is where im lost. If I have
somethig in DT SETUP or DT MEETINGS or DT MAINTENANCE I need to subtract the
numbers from those three things from EMPLOYEE TIME. I also have this "=[DT
REGULAR]/[EMPLOYEE TIME]" in a text box on a form that just shows the percent
and means nothing and I would need to change that formula as well to do the
same thing. here is what I have in the field of my query "Delay Percent:
Sum([DT REGULAR])/Sum([EMPLOYEE TIME])" and thats what what I have so far...
Any help would be greatly appreciated!

Thanks,
Chad

 

[Guest]

Sorry John, The DT SETUP, DT MEETING and the DT MAINTENANCE could all contain
0.00

Try the following

======= All this should be on one line =========

Sum([DT Regular)/Sum([Employee Time]-Nz([DT SetUP],0)-
Nz([DT Meetings])-NZ([DT Maintenance],0))

===== All the above should be on one line ======

I used the NZ (Field, 0) call in case your fields could contain blanks
(nulls). If any field were null, then the math would return blank. Nz
returns the first argument (the field) unless the first argument is
null. If the first argument is null, the NZ returns the value of the
second argument (zero in the example)


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hello, I have a query that takes DT REGULAR and / it by EMPLOYEE TIME and
gives me a percent. I found out that its conflicting with other numbers and
need to fix it by having it have an if statment or something. it needs to do
this [DT REGULAR]/[EMPLOYEE TIME] then here is where im lost. If I have
somethig in DT SETUP or DT MEETINGS or DT MAINTENANCE I need to subtract the
numbers from those three things from EMPLOYEE TIME. I also have this "=[DT
REGULAR]/[EMPLOYEE TIME]" in a text box on a form that just shows the percent
and means nothing and I would need to change that formula as well to do the
same thing. here is what I have in the field of my query "Delay Percent:
Sum([DT REGULAR])/Sum([EMPLOYEE TIME])" and thats what what I have so far...
Any help would be greatly appreciated!

Thanks,
Chad

 

[Guest]

John,
My mistake I dont know what I was doing but I got it working with the

Delay Percent: Sum([DT Regular)/Sum([Employee Time]-Nz([DT SetUP],0)-Nz([DT
Meetings])-NZ([DT Maintenance],0))

but, now I need to get the text box on a form that does the same thing but
dont save it anywhere just for show. How would I write the formula for the
control source?

Sorry John, The DT SETUP, DT MEETING and the DT MAINTENANCE could all contain
0.00

Try the following

======= All this should be on one line =========

Sum([DT Regular)/Sum([Employee Time]-Nz([DT SetUP],0)-
Nz([DT Meetings])-NZ([DT Maintenance],0))

===== All the above should be on one line ======

I used the NZ (Field, 0) call in case your fields could contain blanks
(nulls). If any field were null, then the math would return blank. Nz
returns the first argument (the field) unless the first argument is
null. If the first argument is null, the NZ returns the value of the
second argument (zero in the example)


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hello, I have a query that takes DT REGULAR and / it by EMPLOYEE TIME and
gives me a percent. I found out that its conflicting with other numbers and
need to fix it by having it have an if statment or something. it needs to do
this [DT REGULAR]/[EMPLOYEE TIME] then here is where im lost. If I have
somethig in DT SETUP or DT MEETINGS or DT MAINTENANCE I need to subtract the
numbers from those three things from EMPLOYEE TIME. I also have this "=[DT
REGULAR]/[EMPLOYEE TIME]" in a text box on a form that just shows the percent
and means nothing and I would need to change that formula as well to do the
same thing. here is what I have in the field of my query "Delay Percent:
Sum([DT REGULAR])/Sum([EMPLOYEE TIME])" and thats what what I have so far...
Any help would be greatly appreciated!

Thanks,
Chad

 

[John W. Vinson]

John,
My mistake I dont know what I was doing but I got it working with the

Delay Percent: Sum([DT Regular)/Sum([Employee Time]-Nz([DT SetUP],0)-Nz([DT
Meetings])-NZ([DT Maintenance],0))

but, now I need to get the text box on a form that does the same thing but
dont save it anywhere just for show. How would I write the formula for the
control source?

The Control Source would just be your new calculated fieldname:

[Delay Percent]


John W. Vinson [MVP]