Trouble with ACCESS report

[m.garza2006]

Hello, this is my first time on Google groups, but have been using all
of your ACCESS advice since about 3 months ago. I have very little
knowledge of ACCESS most of what I've learned, I've picked up from here
and the internet. I have created a database that tracks productivity
time for call center employees, I think I have it all down but the last
piece is driving me nuts. I have a report that is fed by a query that
keeps productivity time plus paid time. All that is working but, the
"boss" is requesting a "rating" for combined time. For instance:

If COMBINED is less than 90% then show a 1
If COMBINED is between 92% and 93% then show a 2
If COMBINED is between 94% and 95% then show a 3
If COMBINED is between 96% and 97% then show a 4
If COMBINED is between 98% and 100% then show a 2

The IIF function is not working, the switch function doesn't work and I
am at my wits end. Does anybody have a way of extracting this
information from the COMBINED field and placing it into an unbound
field called RATING?

Any help is greatly appreciated!

 

[Allen Browne]

Try a text box bound to:
= (([Combined] - 88) \ 2)

You will need to modify that depending on what is actually in the Combined
field. If it is a percentage, add a decimal point in front of the 88. If it
is a string value (e.g. you have a query that uses the Format() function to
generate the value), you will need to change the calculation so it is
numeric. You may also want to modify it to drop the negatives for lower
values.

In this example, your ranges were all the same. If they were more complex
ranges, the solution would be to create another table where you store these
ranges.

 

[m.garza2006]

Ok, I'll try that and get back to you, thank you for your suggestion!

M.Garza

Try a text box bound to:
= (([Combined] - 88) \ 2)

You will need to modify that depending on what is actually in the Combined
field. If it is a percentage, add a decimal point in front of the 88. If it
is a string value (e.g. you have a query that uses the Format() function to
generate the value), you will need to change the calculation so it is
numeric. You may also want to modify it to drop the negatives for lower
values.

In this example, your ranges were all the same. If they were more complex
ranges, the solution would be to create another table where you store these
ranges.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hello, this is my first time on Google groups, but have been using all
of your ACCESS advice since about 3 months ago. I have very little
knowledge of ACCESS most of what I've learned, I've picked up from here
and the internet. I have created a database that tracks productivity
time for call center employees, I think I have it all down but the last
piece is driving me nuts. I have a report that is fed by a query that
keeps productivity time plus paid time. All that is working but, the
"boss" is requesting a "rating" for combined time. For instance:

If COMBINED is less than 90% then show a 1
If COMBINED is between 92% and 93% then show a 2
If COMBINED is between 94% and 95% then show a 3
If COMBINED is between 96% and 97% then show a 4
If COMBINED is between 98% and 100% then show a 2

The IIF function is not working, the switch function doesn't work and I
am at my wits end. Does anybody have a way of extracting this
information from the COMBINED field and placing it into an unbound
field called RATING?

Any help is greatly appreciated!- Hide quoted text -- Show quoted text -

 

[M.Garza]

I tried the info below but no luck.

Thanks anyway, I'll keep searching.

Try a text box bound to:
= (([Combined] - 88) \ 2)

You will need to modify that depending on what is actually in the Combined
field. If it is a percentage, add a decimal point in front of the 88. If it
is a string value (e.g. you have a query that uses the Format() function to
generate the value), you will need to change the calculation so it is
numeric. You may also want to modify it to drop the negatives for lower
values.

In this example, your ranges were all the same. If they were more complex
ranges, the solution would be to create another table where you store these
ranges.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello, this is my first time on Google groups, but have been using all
of your ACCESS advice since about 3 months ago. I have very little
knowledge of ACCESS most of what I've learned, I've picked up from here
and the internet. I have created a database that tracks productivity
time for call center employees, I think I have it all down but the last
piece is driving me nuts. I have a report that is fed by a query that
keeps productivity time plus paid time. All that is working but, the
"boss" is requesting a "rating" for combined time. For instance:

If COMBINED is less than 90% then show a 1
If COMBINED is between 92% and 93% then show a 2
If COMBINED is between 94% and 95% then show a 3
If COMBINED is between 96% and 97% then show a 4
If COMBINED is between 98% and 100% then show a 2

The IIF function is not working, the switch function doesn't work and I
am at my wits end. Does anybody have a way of extracting this
information from the COMBINED field and placing it into an unbound
field called RATING?

Any help is greatly appreciated!

 

[Allen Browne]

What result did you get?

What type of field is "Combined"?
Is it a Number field in a table?
Or a Text field?
Or a calculated field in a query?

(I did assume that in your original example, you wanted a 5 for 98 or 99.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I tried the info below but no luck.

Thanks anyway, I'll keep searching.

Try a text box bound to:
= (([Combined] - 88) \ 2)

You will need to modify that depending on what is actually in the
Combined
field. If it is a percentage, add a decimal point in front of the 88. If
it
is a string value (e.g. you have a query that uses the Format() function
to
generate the value), you will need to change the calculation so it is
numeric. You may also want to modify it to drop the negatives for lower
values.

In this example, your ranges were all the same. If they were more complex
ranges, the solution would be to create another table where you store
these
ranges.

Hello, this is my first time on Google groups, but have been using all
of your ACCESS advice since about 3 months ago. I have very little
knowledge of ACCESS most of what I've learned, I've picked up from here
and the internet. I have created a database that tracks productivity
time for call center employees, I think I have it all down but the last
piece is driving me nuts. I have a report that is fed by a query that
keeps productivity time plus paid time. All that is working but, the
"boss" is requesting a "rating" for combined time. For instance:

If COMBINED is less than 90% then show a 1
If COMBINED is between 92% and 93% then show a 2
If COMBINED is between 94% and 95% then show a 3
If COMBINED is between 96% and 97% then show a 4
If COMBINED is between 98% and 100% then show a 2

The IIF function is not working, the switch function doesn't work and I
am at my wits end. Does anybody have a way of extracting this
information from the COMBINED field and placing it into an unbound
field called RATING?

Any help is greatly appreciated!

 

[M.Garza]

Thanks for responding, yes you are correct I want a 5 for anything
between 98 and 100. The information stored in the COMBINED box is
online time / paid time.

For example (online time) 7.24 / 7.50 (paid time) = 97%

In my report I have a text box named COMBINED that stores the 97% value
based on a query feeding the report. I want another text box that
gives me a 1, 2, 3, 4, or 5 as per the previous criteria.

As for the result, I added a text box bound to:
= (([Combined] - 88.) \ 2) (I added the . before the 88 as you
suggested)

When i run the report the first record states 98% COMBINED and the text
box ( = (([Combined] - 88.) \ 2)) shows a -43, the next record is 87%,
text box shows -43, I also have records that are less than 90 and more
than 100 for instance 2% and 104%, the text box there shows a -44 and
-43.

In addition, I tried the nested IIF function:

=IIf([COMBINED]>=91,1,IIf([COMBINED] Between 92 And 93,2,IIf([COMBINED]
Between 94 And 95,3,IIf([COMBINED] Between 96 And 97,4,IIf([COMBINED]
Between 98 And 100,5,IIf([COMBINED]<=101,0))))))

and I get a 0 for all the records, if I remove the last statement
iif([COMBINED] <=100,0) I get nothing.

Any help is greatly appreciated.

What result did you get?

What type of field is "Combined"?
Is it a Number field in a table?
Or a Text field?
Or a calculated field in a query?

(I did assume that in your original example, you wanted a 5 for 98 or 99.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I tried the info below but no luck.

Thanks anyway, I'll keep searching.

Try a text box bound to:
= (([Combined] - 88) \ 2)

You will need to modify that depending on what is actually in the
Combined
field. If it is a percentage, add a decimal point in front of the 88. If
it
is a string value (e.g. you have a query that uses the Format() function
to
generate the value), you will need to change the calculation so it is
numeric. You may also want to modify it to drop the negatives for lower
values.

In this example, your ranges were all the same. If they were more complex
ranges, the solution would be to create another table where you store
these
ranges.

Hello, this is my first time on Google groups, but have been using all
of your ACCESS advice since about 3 months ago. I have very little
knowledge of ACCESS most of what I've learned, I've picked up from here
and the internet. I have created a database that tracks productivity
time for call center employees, I think I have it all down but the last
piece is driving me nuts. I have a report that is fed by a query that
keeps productivity time plus paid time. All that is working but, the
"boss" is requesting a "rating" for combined time. For instance:

If COMBINED is less than 90% then show a 1
If COMBINED is between 92% and 93% then show a 2
If COMBINED is between 94% and 95% then show a 3
If COMBINED is between 96% and 97% then show a 4
If COMBINED is between 98% and 100% then show a 2

The IIF function is not working, the switch function doesn't work and I
am at my wits end. Does anybody have a way of extracting this
information from the COMBINED field and placing it into an unbound
field called RATING?

Any help is greatly appreciated!

 

[Allen Browne]

Okay, it looks like your numbers are genuine percents, and will need to be
multiplied by 100 to get the desired result:
= ((100 * [Combined] - 88) \ 2)

If you have numbers below 90% and greater than 100%, you have values outside
of just those ranges. In this case, you will probably need to teach Access
about the valid ranges by creating another table that stores the ranges. You
can then match the query results against this table to get the correct
range.

There's a bit more work in setting that up, but Tom Ellison explains how to
do it here:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for responding, yes you are correct I want a 5 for anything
between 98 and 100. The information stored in the COMBINED box is
online time / paid time.

For example (online time) 7.24 / 7.50 (paid time) = 97%

In my report I have a text box named COMBINED that stores the 97% value
based on a query feeding the report. I want another text box that
gives me a 1, 2, 3, 4, or 5 as per the previous criteria.

As for the result, I added a text box bound to:
= (([Combined] - 88.) \ 2) (I added the . before the 88 as you
suggested)

When i run the report the first record states 98% COMBINED and the text
box ( = (([Combined] - 88.) \ 2)) shows a -43, the next record is 87%,
text box shows -43, I also have records that are less than 90 and more
than 100 for instance 2% and 104%, the text box there shows a -44 and
-43.

In addition, I tried the nested IIF function:

=IIf([COMBINED]>=91,1,IIf([COMBINED] Between 92 And 93,2,IIf([COMBINED]
Between 94 And 95,3,IIf([COMBINED] Between 96 And 97,4,IIf([COMBINED]
Between 98 And 100,5,IIf([COMBINED]<=101,0))))))

and I get a 0 for all the records, if I remove the last statement
iif([COMBINED] <=100,0) I get nothing.

Any help is greatly appreciated.

What result did you get?

What type of field is "Combined"?
Is it a Number field in a table?
Or a Text field?
Or a calculated field in a query?

(I did assume that in your original example, you wanted a 5 for 98 or
99.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I tried the info below but no luck.

Thanks anyway, I'll keep searching.

Allen Browne wrote:
Try a text box bound to:
= (([Combined] - 88) \ 2)

You will need to modify that depending on what is actually in the
Combined
field. If it is a percentage, add a decimal point in front of the 88.
If
it
is a string value (e.g. you have a query that uses the Format()
function
to
generate the value), you will need to change the calculation so it is
numeric. You may also want to modify it to drop the negatives for
lower
values.

In this example, your ranges were all the same. If they were more
complex
ranges, the solution would be to create another table where you store
these
ranges.

Hello, this is my first time on Google groups, but have been using
all
of your ACCESS advice since about 3 months ago. I have very little
knowledge of ACCESS most of what I've learned, I've picked up from
here
and the internet. I have created a database that tracks
productivity
time for call center employees, I think I have it all down but the
last
piece is driving me nuts. I have a report that is fed by a query
that
keeps productivity time plus paid time. All that is working but,
the
"boss" is requesting a "rating" for combined time. For instance:

If COMBINED is less than 90% then show a 1
If COMBINED is between 92% and 93% then show a 2
If COMBINED is between 94% and 95% then show a 3
If COMBINED is between 96% and 97% then show a 4
If COMBINED is between 98% and 100% then show a 2

The IIF function is not working, the switch function doesn't work
and I
am at my wits end. Does anybody have a way of extracting this
information from the COMBINED field and placing it into an unbound
field called RATING?

Any help is greatly appreciated!

 

[M.Garza]

Thanks!

The formula worked but I do have numbers outside of the 90 and 100
below and over. I will look at the Tom Ellison example and see what I
can figure out.

Again, thank you so much for your help!!

Maria

Okay, it looks like your numbers are genuine percents, and will need to be
multiplied by 100 to get the desired result:
= ((100 * [Combined] - 88) \ 2)

If you have numbers below 90% and greater than 100%, you have values outside
of just those ranges. In this case, you will probably need to teach Access
about the valid ranges by creating another table that stores the ranges. You
can then match the query results against this table to get the correct
range.

There's a bit more work in setting that up, but Tom Ellison explains how to
do it here:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for responding, yes you are correct I want a 5 for anything
between 98 and 100. The information stored in the COMBINED box is
online time / paid time.

For example (online time) 7.24 / 7.50 (paid time) = 97%

In my report I have a text box named COMBINED that stores the 97% value
based on a query feeding the report. I want another text box that
gives me a 1, 2, 3, 4, or 5 as per the previous criteria.

As for the result, I added a text box bound to:
= (([Combined] - 88.) \ 2) (I added the . before the 88 as you
suggested)

When i run the report the first record states 98% COMBINED and the text
box ( = (([Combined] - 88.) \ 2)) shows a -43, the next record is 87%,
text box shows -43, I also have records that are less than 90 and more
than 100 for instance 2% and 104%, the text box there shows a -44 and
-43.

In addition, I tried the nested IIF function:

=IIf([COMBINED]>=91,1,IIf([COMBINED] Between 92 And 93,2,IIf([COMBINED]
Between 94 And 95,3,IIf([COMBINED] Between 96 And 97,4,IIf([COMBINED]
Between 98 And 100,5,IIf([COMBINED]<=101,0))))))

and I get a 0 for all the records, if I remove the last statement
iif([COMBINED] <=100,0) I get nothing.

Any help is greatly appreciated.

What result did you get?

What type of field is "Combined"?
Is it a Number field in a table?
Or a Text field?
Or a calculated field in a query?

(I did assume that in your original example, you wanted a 5 for 98 or
99.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I tried the info below but no luck.

Thanks anyway, I'll keep searching.

Allen Browne wrote:
Try a text box bound to:
= (([Combined] - 88) \ 2)

You will need to modify that depending on what is actually in the
Combined
field. If it is a percentage, add a decimal point in front of the 88.
If
it
is a string value (e.g. you have a query that uses the Format()
function
to
generate the value), you will need to change the calculation so it is
numeric. You may also want to modify it to drop the negatives for
lower
values.

In this example, your ranges were all the same. If they were more
complex
ranges, the solution would be to create another table where you store
these
ranges.

Hello, this is my first time on Google groups, but have been using
all
of your ACCESS advice since about 3 months ago. I have very little
knowledge of ACCESS most of what I've learned, I've picked up from
here
and the internet. I have created a database that tracks
productivity
time for call center employees, I think I have it all down but the
last
piece is driving me nuts. I have a report that is fed by a query
that
keeps productivity time plus paid time. All that is working but,
the
"boss" is requesting a "rating" for combined time. For instance:

If COMBINED is less than 90% then show a 1
If COMBINED is between 92% and 93% then show a 2
If COMBINED is between 94% and 95% then show a 3
If COMBINED is between 96% and 97% then show a 4
If COMBINED is between 98% and 100% then show a 2

The IIF function is not working, the switch function doesn't work
and I
am at my wits end. Does anybody have a way of extracting this
information from the COMBINED field and placing it into an unbound
field called RATING?

Any help is greatly appreciated!