Query/Report set up confusion

[MTFlyer]

I have a table that has productivity standards by work type and units. I
have a separate table that is a productivity log where individual daily
productivity data are entered. I have a report that was set up by someone
else that uses a query to calculate for a set date range (for example
01/01/09 to 01/15/09) their productivity by hour for each function (for
example they prepped 30 inches of documents in 10 hours the result is they
prepped at 3 inches per hour). The standards for prepping are 2.75 minimum
and 3.5 expert.

I want a report to calculate the individual associate's productivity, then
compare it to minimum and expert and come back with a Met or Not Met. I'm
unable to figure out how to add the new Standards table to the original
report so that I can do this comparison. Anyone have any suggestions ?

This is a database I inherited and am trying to expand upon/improve. It is
currently in Access 2003. I have Access 2003 on one PC and 2007 on another.
The database needs to remain in 2003 as not everyone at the office has the
newer version.

 

[MTFlyer]

UPDATE - I found on the Report form she was using text boxes to calculate the
results of the productivity per hour. I added 2 different text boxes,
MinimumMet and ExperiencedMet with the following if statement in it - but
still getting some errors, any suggestion Productivity is the name of the
text box that has the resulting # of the function per hour calculation:

If([Productivity])<2.75,"Not Met","Met"
If([Productivity])>3.5,"Not Met","Met"

 

[Duane Hookom]

You should store your productivity standards in a table. WHEN they change,
you shouldn't have to go around looking for expressions in queries and
reports.

Otherwise your expressions are wonky. Assuming Productivity is calculated in
the query, rty something like:

=IIf([Productivity]<2.75,"Not Met","Met")
=IIf([Productivity]>3.5,"Show off","Ho Hum")

Again, you shouldn't hard-code 2.75 and 3.5 into your expressions. At a
minimum you could create a small user-defined function and save it in a
module of business calcs.

--
Duane Hookom
Microsoft Access MVP

UPDATE - I found on the Report form she was using text boxes to calculate the
results of the productivity per hour. I added 2 different text boxes,
MinimumMet and ExperiencedMet with the following if statement in it - but
still getting some errors, any suggestion Productivity is the name of the
text box that has the resulting # of the function per hour calculation:

If([Productivity])<2.75,"Not Met","Met"
If([Productivity])>3.5,"Not Met","Met"

I have a table that has productivity standards by work type and units. I
have a separate table that is a productivity log where individual daily
productivity data are entered. I have a report that was set up by someone
else that uses a query to calculate for a set date range (for example
01/01/09 to 01/15/09) their productivity by hour for each function (for
example they prepped 30 inches of documents in 10 hours the result is they
prepped at 3 inches per hour). The standards for prepping are 2.75 minimum
and 3.5 expert.

I want a report to calculate the individual associate's productivity, then
compare it to minimum and expert and come back with a Met or Not Met. I'm
unable to figure out how to add the new Standards table to the original
report so that I can do this comparison. Anyone have any suggestions ?

This is a database I inherited and am trying to expand upon/improve. It is
currently in Access 2003. I have Access 2003 on one PC and 2007 on another.
The database needs to remain in 2003 as not everyone at the office has the
newer version.

 

[MTFlyer]

I do have the standards in a separate table. I should have indicated in my
original post that I was needing to get 1 specific report out for an
employee's eval so I was playing around with that specific standard. I have
a table that lists functions like this:
Function PatientType Minimum Expert
Prep Al 2.75 3.5
Analysis IP 8 10.7
Analysis OP 15 21.4
Transcr Al 200 300
etc.

My productivity log, we have required fields for start/stop time to
calculate the duration, function and patient type to determine if we are
talking lines, pages, inches, etc.

The query currently figures based on the date frame you enter, the total
number of work units for each function and the quantity per hour. Now I'm
trying to add the met/not met standard. I was able to get this 1 report run
yesterday, but still working on getting it to work with the 2 tables so that
no matter what function the associate has it will determine if they have or
have not met the standards.

You should store your productivity standards in a table. WHEN they change,
you shouldn't have to go around looking for expressions in queries and
reports.

Otherwise your expressions are wonky. Assuming Productivity is calculated in
the query, rty something like:

=IIf([Productivity]<2.75,"Not Met","Met")
=IIf([Productivity]>3.5,"Show off","Ho Hum")

Again, you shouldn't hard-code 2.75 and 3.5 into your expressions. At a
minimum you could create a small user-defined function and save it in a
module of business calcs.

--
Duane Hookom
Microsoft Access MVP

UPDATE - I found on the Report form she was using text boxes to calculate the
results of the productivity per hour. I added 2 different text boxes,
MinimumMet and ExperiencedMet with the following if statement in it - but
still getting some errors, any suggestion Productivity is the name of the
text box that has the resulting # of the function per hour calculation:

If([Productivity])<2.75,"Not Met","Met"
If([Productivity])>3.5,"Not Met","Met"

I have a table that has productivity standards by work type and units. I
have a separate table that is a productivity log where individual daily
productivity data are entered. I have a report that was set up by someone
else that uses a query to calculate for a set date range (for example
01/01/09 to 01/15/09) their productivity by hour for each function (for
example they prepped 30 inches of documents in 10 hours the result is they
prepped at 3 inches per hour). The standards for prepping are 2.75 minimum
and 3.5 expert.

I want a report to calculate the individual associate's productivity, then
compare it to minimum and expert and come back with a Met or Not Met. I'm
unable to figure out how to add the new Standards table to the original
report so that I can do this comparison. Anyone have any suggestions ?

This is a database I inherited and am trying to expand upon/improve. It is
currently in Access 2003. I have Access 2003 on one PC and 2007 on another.
The database needs to remain in 2003 as not everyone at the office has the
newer version.

 

[Duane Hookom]

Have you tried creating a totals query with the two tables joining Function
and PatientType fields, Divide the Sum(quantity) by the Sum of the time
difference, and determining if the result is more, between, or less than the
ranges?

--
Duane Hookom
Microsoft Access MVP

I do have the standards in a separate table. I should have indicated in my
original post that I was needing to get 1 specific report out for an
employee's eval so I was playing around with that specific standard. I have
a table that lists functions like this:
Function PatientType Minimum Expert
Prep Al 2.75 3.5
Analysis IP 8 10.7
Analysis OP 15 21.4
Transcr Al 200 300
etc.

My productivity log, we have required fields for start/stop time to
calculate the duration, function and patient type to determine if we are
talking lines, pages, inches, etc.

The query currently figures based on the date frame you enter, the total
number of work units for each function and the quantity per hour. Now I'm
trying to add the met/not met standard. I was able to get this 1 report run
yesterday, but still working on getting it to work with the 2 tables so that
no matter what function the associate has it will determine if they have or
have not met the standards.

You should store your productivity standards in a table. WHEN they change,
you shouldn't have to go around looking for expressions in queries and
reports.

Otherwise your expressions are wonky. Assuming Productivity is calculated in
the query, rty something like:

=IIf([Productivity]<2.75,"Not Met","Met")
=IIf([Productivity]>3.5,"Show off","Ho Hum")

Again, you shouldn't hard-code 2.75 and 3.5 into your expressions. At a
minimum you could create a small user-defined function and save it in a
module of business calcs.

--
Duane Hookom
Microsoft Access MVP

UPDATE - I found on the Report form she was using text boxes to calculate the
results of the productivity per hour. I added 2 different text boxes,
MinimumMet and ExperiencedMet with the following if statement in it - but
still getting some errors, any suggestion Productivity is the name of the
text box that has the resulting # of the function per hour calculation:

If([Productivity])<2.75,"Not Met","Met"
If([Productivity])>3.5,"Not Met","Met"

:

I have a table that has productivity standards by work type and units. I
have a separate table that is a productivity log where individual daily
productivity data are entered. I have a report that was set up by someone
else that uses a query to calculate for a set date range (for example
01/01/09 to 01/15/09) their productivity by hour for each function (for
example they prepped 30 inches of documents in 10 hours the result is they
prepped at 3 inches per hour). The standards for prepping are 2.75 minimum
and 3.5 expert.

I want a report to calculate the individual associate's productivity, then
compare it to minimum and expert and come back with a Met or Not Met. I'm
unable to figure out how to add the new Standards table to the original
report so that I can do this comparison. Anyone have any suggestions ?

This is a database I inherited and am trying to expand upon/improve. It is
currently in Access 2003. I have Access 2003 on one PC and 2007 on another.
The database needs to remain in 2003 as not everyone at the office has the
newer version.