Comparing Actual Hours to a Target

[AuditorCMM]

We have a database called "Time" that tracks each employee's time and want
to start comparing an employee's total YTD hours to a target. Currently, we
have a report (based on a query) that details the employee's total time as of
a given date. It is based on dates entered by the user since the query uses
the following criteria - Between [Beginning Date:] And [Ending Date:].

Here is my dilemma. At any given ending date, we want the report to also
display the total target hours. I have already created a table called
"Target" that shows every date of the year in one column and the total target
hours for that date in the 2nd column. Would there be a way to add a field
to the query/report that would display the targeted hours value from the
"Target" table based on the ending date entered? If not, would there be a
way I could somehow add a field to the report that would show the appropriate
target value? Or maybe neither of these options is the right answer. I
guess I'm just not sure how to correlate the 2 tables to get what I want.
Any help would be much appreciated! Thanks!!

 

[Marshall Barton]

We have a database called "Time" that tracks each employee's time and want
to start comparing an employee's total YTD hours to a target. Currently, we
have a report (based on a query) that details the employee's total time as of
a given date. It is based on dates entered by the user since the query uses
the following criteria - Between [Beginning Date:] And [Ending Date:].

Here is my dilemma. At any given ending date, we want the report to also
display the total target hours. I have already created a table called
"Target" that shows every date of the year in one column and the total target
hours for that date in the 2nd column. Would there be a way to add a field
to the query/report that would display the targeted hours value from the
"Target" table based on the ending date entered? If not, would there be a
way I could somehow add a field to the report that would show the appropriate
target value? Or maybe neither of these options is the right answer. I
guess I'm just not sure how to correlate the 2 tables to get what I want.

You can use a text box with an expression like:

=DLookup("[Target hours]", "Target", "[target date]=" &
Format([Ending Date:], "\#m\/d\/yyyy\#")

 

[AuditorCMM]

That worked perfectly. Thanks!!

We have a database called "Time" that tracks each employee's time and want
to start comparing an employee's total YTD hours to a target. Currently, we
have a report (based on a query) that details the employee's total time as of
a given date. It is based on dates entered by the user since the query uses
the following criteria - Between [Beginning Date:] And [Ending Date:].

Here is my dilemma. At any given ending date, we want the report to also
display the total target hours. I have already created a table called
"Target" that shows every date of the year in one column and the total target
hours for that date in the 2nd column. Would there be a way to add a field
to the query/report that would display the targeted hours value from the
"Target" table based on the ending date entered? If not, would there be a
way I could somehow add a field to the report that would show the appropriate
target value? Or maybe neither of these options is the right answer. I
guess I'm just not sure how to correlate the 2 tables to get what I want.

You can use a text box with an expression like:

=DLookup("[Target hours]", "Target", "[target date]=" &
Format([Ending Date:], "\#m\/d\/yyyy\#")