Calculated Field in Query

[roarcrm]

Hello,

I have a form called Project that has a sub-form called Task. Within the task form I have a calculation field to calculate the total time spent in all tasks within the project, the calculation is: =Sum([TimeSpent]). Then, within the project form I have another calculation field to deduct this total from the amount of hours allocated to a project to give the hours remaining - the calculation is: =[HoursAllocated]-[Task].[Form]![TimeSpentCalculationField].

Now, as these values are calculated on the form they are not in the tables and I need to find a way to use this in queries, e.g. search for projects where hours remaining is less than 10 hours for example.

What is the best/easiest way to do this for both myself and other users?

[rpeare]

if you have a form/subform arrangment your tables must follow the same type of arrangment table/subtable (i.e. orders and line items for that order). So the easiest way is to create an aggregate query on your subtable that is sum([TimeSpent]) as one field and the FK of the table as the second field. This will give you the total timespent for each item on the main table.

Then create a query based on the main table, add the query you just created perform your subtraction in a third field and put the criteria in that third field.

There are ways to do this as a single query but I prefer the subquery method so I can modify it easily when necessary.