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
|
|||
|
|||
Update query formula
I have data that includes hours per month by job category. In my
months table I also have a column that is the realized hours for each month. (hours available less vacation or holiday days in that month). I created a query that takes the hours each month by job category and divides by the realized hours to get a full time equivalent calculation. That all works fine and provides an fte each month by job category. now I wish to total that. I cannot simply total the row, as a 1 fte/ month would give 12 fte's in a year. Its really only 1 FTE. The next best thing would be to average, but that doesn't get the right data. the best thing (say I want to run a report in November for Oct and Nov data),is to sum the total hours charged by the total realized hours for the 2 months only. Not sure how to automatically do this. I guess I could add each succcessive month realized hours in that table and sum that column. I could create a report for each month ahead of time and then choose that report (formulas built into the report ahead of time). or some other method? I also need help to figure out how to build this expression? |
#2
|
|||
|
|||
Update query formula
I created a query that takes the hours each month by job category and
divides by the realized hours to get a full time equivalent calculation. I follow the above but not the other where you talk about 12 months and two months. Do you want comparrison from month to month? -- KARL DEWEY Build a little - Test a little |
#3
|
|||
|
|||
Update query formula
On Oct 23, 1:00*pm, KARL DEWEY
wrote: I created a query that takes the hours each month by job category and divides by the realized hours to get a full time equivalent calculation. I follow the above but not the other where you talk about 12 months and two months. *Do you want comparrison from month to month? -- KARL DEWEY Build a little - Test a little Each month, I need the formula to change. So for November, it needs to be (realized hours for month 1 and month 2) / (actual hours for month 1 and month 2). (fiscal calendar is Oct-Sept) For Dec. I need to add a month at the end of each of those. For actual hours, since the database has total hours for the year, it will always be actual hours year to date. The realized hours, though is listed on a table by month. I guess I could go into that table manually and add the realized hours for Dec (for example) and then use the total for that column in the formula. Otherwise I don't know how to have the formula each month end only get realized hours for (Oct, Nov and Dec) only. (using examples of November month end and December month end). |
#4
|
|||
|
|||
Update query formula
Ok, it sounds like you have two tables. Post the table structure, field
names and datatype. Post sample data. -- KARL DEWEY Build a little - Test a little "bizee" wrote: On Oct 23, 1:00 pm, KARL DEWEY wrote: I created a query that takes the hours each month by job category and divides by the realized hours to get a full time equivalent calculation. I follow the above but not the other where you talk about 12 months and two months. Do you want comparrison from month to month? -- KARL DEWEY Build a little - Test a little Each month, I need the formula to change. So for November, it needs to be (realized hours for month 1 and month 2) / (actual hours for month 1 and month 2). (fiscal calendar is Oct-Sept) For Dec. I need to add a month at the end of each of those. For actual hours, since the database has total hours for the year, it will always be actual hours year to date. The realized hours, though is listed on a table by month. I guess I could go into that table manually and add the realized hours for Dec (for example) and then use the total for that column in the formula. Otherwise I don't know how to have the formula each month end only get realized hours for (Oct, Nov and Dec) only. (using examples of November month end and December month end). |
#5
|
|||
|
|||
Update query formula
On Mon, 27 Oct 2008 09:04:20 -0700 (PDT), bizee wrote:
Each month, I need the formula to change. So for November, it needs to be (realized hours for month 1 and month 2) / (actual hours for month 1 and month 2). (fiscal calendar is Oct-Sept) Well, you don't need the formula to change - you just need a better formula, one which ascertains the current month and derives the values needed based on that. If you have twelve fields in your table for realized hours, you're "committing spreadsheet" and need to normalize your tables. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|