Need help with Report formula

[Guest]

Hello, I have this formula:

=Abs(Sum([Shift]=1)*[DT REGULAR])/Abs(Sum([Shift]=1)*[EMPLOYEE TIME])

in a text box in a report just showing a total for shift 1 thats on the
report. I want to change it to show by Supervisor and name like this:

=Abs(Sum([Supervisor]=Bill Black)*[DT REGULAR])/Abs(Sum([Supervisor]=Bill
Black)*[EMPLOYEE TIME])

It keeps giving me an error when I change the formula. (ERROR- The
expression you entered contains invalid syntay. You may have entered an
operand without an operator.

What do I need to add to the formula?

Thanks!

 

[John Spencer]

Since you are checking a string, you need quotes around the string value.

=Abs(Sum([Supervisor]="Bill Black") * [DT REGULAR]) /
Abs(Sum([Supervisor]="Bill Black")*[EMPLOYEE TIME])

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

 

[Guest]

That worked great but I have two other ones simular to this one thats giving
the wrong totals.
I have:
=Sum(Abs([Shift]=1)*[EMPLOYEE TIME])
Changed to:
=Sum(Abs([Supervisor]="Bill Black")*[EMPLOYEE TIME])
and it gives a higher total time? I also have:
=Sum(Abs([Shift]=1)*[FOOTAGE])
Changed to:
=Sum(Abs([Supervisor]="Bill Black")*[FOOTAGE])
Ant it now gives a higher footage result?

What do I need to add to these to get the right results? The old way worked
and with the correct totals. Thanks!!

Since you are checking a string, you need quotes around the string value.

=Abs(Sum([Supervisor]="Bill Black") * [DT REGULAR]) /
Abs(Sum([Supervisor]="Bill Black")*[EMPLOYEE TIME])

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

Hello, I have this formula:

=Abs(Sum([Shift]=1)*[DT REGULAR])/Abs(Sum([Shift]=1)*[EMPLOYEE TIME])

in a text box in a report just showing a total for shift 1 thats on the
report. I want to change it to show by Supervisor and name like this:

=Abs(Sum([Supervisor]=Bill Black)*[DT REGULAR])/Abs(Sum([Supervisor]=Bill
Black)*[EMPLOYEE TIME])

It keeps giving me an error when I change the formula. (ERROR- The
expression you entered contains invalid syntay. You may have entered an
operand without an operator.

What do I need to add to the formula?

Thanks!

 

[John Spencer]

I would suspect that Supervisor = "Bill Black" and Shift =1 are not
returning the same records.

Is it possible that Bill Black is also a supervisor on a different shift or
that he has personnel that he supervises that are on a different shift?


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

That worked great but I have two other ones simular to this one thats
giving
the wrong totals.
I have:
=Sum(Abs([Shift]=1)*[EMPLOYEE TIME])
Changed to:
=Sum(Abs([Supervisor]="Bill Black")*[EMPLOYEE TIME])
and it gives a higher total time? I also have:
=Sum(Abs([Shift]=1)*[FOOTAGE])
Changed to:
=Sum(Abs([Supervisor]="Bill Black")*[FOOTAGE])
Ant it now gives a higher footage result?

What do I need to add to these to get the right results? The old way
worked
and with the correct totals. Thanks!!

Since you are checking a string, you need quotes around the string value.

=Abs(Sum([Supervisor]="Bill Black") * [DT REGULAR]) /
Abs(Sum([Supervisor]="Bill Black")*[EMPLOYEE TIME])

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

Hello, I have this formula:

=Abs(Sum([Shift]=1)*[DT REGULAR])/Abs(Sum([Shift]=1)*[EMPLOYEE TIME])

in a text box in a report just showing a total for shift 1 thats on the
report. I want to change it to show by Supervisor and name like this:

=Abs(Sum([Supervisor]=Bill Black)*[DT
REGULAR])/Abs(Sum([Supervisor]=Bill
Black)*[EMPLOYEE TIME])

It keeps giving me an error when I change the formula. (ERROR- The
expression you entered contains invalid syntay. You may have entered an
operand without an operator.

What do I need to add to the formula?

Thanks!

 

[Guest]

The report is based on three shifts (Shift 1, Shift 2, Shift 3) Each shift
has a supervisor and we rotate with our crew once a week. This report brings
up totals for a date range so I now cant get my totals from a shift I now
have to get them by supervisor because if I am on shift 1 for one week then
shift 3 the next and the supervisormoves to the shift 1 then it will add his
totals with mine itf the report is ran for a two week range. Hope this
helps....

I would suspect that Supervisor = "Bill Black" and Shift =1 are not
returning the same records.

Is it possible that Bill Black is also a supervisor on a different shift or
that he has personnel that he supervises that are on a different shift?


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

That worked great but I have two other ones simular to this one thats
giving
the wrong totals.
I have:
=Sum(Abs([Shift]=1)*[EMPLOYEE TIME])
Changed to:
=Sum(Abs([Supervisor]="Bill Black")*[EMPLOYEE TIME])
and it gives a higher total time? I also have:
=Sum(Abs([Shift]=1)*[FOOTAGE])
Changed to:
=Sum(Abs([Supervisor]="Bill Black")*[FOOTAGE])
Ant it now gives a higher footage result?

What do I need to add to these to get the right results? The old way
worked
and with the correct totals. Thanks!!

Since you are checking a string, you need quotes around the string value.

=Abs(Sum([Supervisor]="Bill Black") * [DT REGULAR]) /
Abs(Sum([Supervisor]="Bill Black")*[EMPLOYEE TIME])

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

Hello, I have this formula:

=Abs(Sum([Shift]=1)*[DT REGULAR])/Abs(Sum([Shift]=1)*[EMPLOYEE TIME])

in a text box in a report just showing a total for shift 1 thats on the
report. I want to change it to show by Supervisor and name like this:

=Abs(Sum([Supervisor]=Bill Black)*[DT
REGULAR])/Abs(Sum([Supervisor]=Bill
Black)*[EMPLOYEE TIME])

It keeps giving me an error when I change the formula. (ERROR- The
expression you entered contains invalid syntay. You may have entered an
operand without an operator.

What do I need to add to the formula?

Thanks!

 

[John Spencer]

Sorry, I really don't understand your data structure, but your problem is
almost certainly being caused by the data in your tables and how it gets
selected.

Beyond that I have no clue on how to fix your problem.

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

The report is based on three shifts (Shift 1, Shift 2, Shift 3) Each
shift
has a supervisor and we rotate with our crew once a week. This report
brings
up totals for a date range so I now cant get my totals from a shift I now
have to get them by supervisor because if I am on shift 1 for one week
then
shift 3 the next and the supervisormoves to the shift 1 then it will add
his
totals with mine itf the report is ran for a two week range. Hope this
helps....

I would suspect that Supervisor = "Bill Black" and Shift =1 are not
returning the same records.

Is it possible that Bill Black is also a supervisor on a different shift
or
that he has personnel that he supervises that are on a different shift?


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

That worked great but I have two other ones simular to this one thats
giving
the wrong totals.
I have:
=Sum(Abs([Shift]=1)*[EMPLOYEE TIME])
Changed to:
=Sum(Abs([Supervisor]="Bill Black")*[EMPLOYEE TIME])
and it gives a higher total time? I also have:
=Sum(Abs([Shift]=1)*[FOOTAGE])
Changed to:
=Sum(Abs([Supervisor]="Bill Black")*[FOOTAGE])
Ant it now gives a higher footage result?

What do I need to add to these to get the right results? The old way
worked
and with the correct totals. Thanks!!

:

Since you are checking a string, you need quotes around the string
value.

=Abs(Sum([Supervisor]="Bill Black") * [DT REGULAR]) /
Abs(Sum([Supervisor]="Bill Black")*[EMPLOYEE TIME])

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

Hello, I have this formula:

=Abs(Sum([Shift]=1)*[DT REGULAR])/Abs(Sum([Shift]=1)*[EMPLOYEE
TIME])

in a text box in a report just showing a total for shift 1 thats on
the
report. I want to change it to show by Supervisor and name like
this:

=Abs(Sum([Supervisor]=Bill Black)*[DT
REGULAR])/Abs(Sum([Supervisor]=Bill
Black)*[EMPLOYEE TIME])

It keeps giving me an error when I change the formula. (ERROR- The
expression you entered contains invalid syntay. You may have entered
an
operand without an operator.

What do I need to add to the formula?

Thanks!

 

[Guest]

Everyone, Thanks for the support but I got it fixed! I wanted to use the
formula in a text box on the rpt itself to calculate the totals from each
supervisor. I used:

=Abs(Sum([Supervisor]="supervisors name")*[DT
REGULAR])/Abs(Sum([Supervisor]=" supervisors name ")*[EMPLOYEE TIME])

=Sum(Abs([Supervisor]=" supervisors name ")*[EMPLOYEE TIME])

=Sum(Abs([Supervisor]=" supervisors name ")*[FOOTAGE])


All good....Thanks!