If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Comparing Actual Hours to a Target
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!! |
#2
|
|||
|
|||
Comparing Actual Hours to a Target
AuditorCMM wrote:
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\#") -- Marsh MVP [MS Access] |
#3
|
|||
|
|||
Comparing Actual Hours to a Target
That worked perfectly. Thanks!!
"Marshall Barton" wrote: AuditorCMM wrote: 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\#") -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|