Update query formula

[bizee]

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?

 

[KARL DEWEY]

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?

 

[bizee]

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?

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).

 

[KARL DEWEY]

Ok, it sounds like you have two tables. Post the table structure, field
names and datatype. Post sample data.

 

[John W. Vinson]

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.