Query Design - Calculated Fields

[roarcrm]

Hello all,



I have a form layout as displayed here with a Task form embedded inside a Project form - each project can have many tasks and for each task the Time Used is subtracted from the total Hours Allocated which gradually decreases. What I want to do is to have a query set up that displays all projects with the Hours Remaining field displayed.

I have tried to play about with this and the only time I got the hours remaining values to display in the query results it seemed to display multiple lines for every task rather than one line for the whole project. Can somebody point me in the right direction here? Many thanks in advance.

[rpeare]

First, create a query that sums the TIMESPENT field by the PK/FK related field between your form/subform. This will give you the total time used for each project.

Then create another query based on the table driving your 'main' form link it to the secondary query you created through the PK/FK field you should be able to see the time alloted and time used, then you can calculate the time remaining.

[roarcrm]

First, create a query that sums the TIMESPENT field by the PK/FK related field between your form/subform. This will give you the total time used for each project.

Then create another query based on the table driving your 'main' form link it to the secondary query you created through the PK/FK field you should be able to see the time alloted and time used, then you can calculate the time remaining.

Thanks for that! I understand what you mean, sorry to sound daft but how would I create the query to sum the Time Spent field? The PK in the Project table is ProjectIdentifier which is the FK in the Task table. The PK in the Task table is TaskIdentifier.

[rpeare]

it's called an aggregate query. Look for the sigma button in your query design (looks like a capital E). It will add a row to your query design area called TOTAL or TOTALS the default is GROUP BY, change that to SUM, when included with the FK field it will sum all the time used for the project.

[roarcrm]

it's called an aggregate query. Look for the sigma button in your query design (looks like a capital E). It will add a row to your query design area called TOTAL or TOTALS the default is GROUP BY, change that to SUM, when included with the FK field it will sum all the time used for the project.

Thanks! How do I include this in the second query?

[rpeare]

I told you how in the first post.

You have two tables one is your 'main' one is your 'sub'
Similar to your form constrution, your 'main' form employs the 'main' table, the subform employs the 'sub' table.

You aggregate the data in the 'sub' table using the FK (foreign key) of your main table.
Once this query is written and correctly summing your values for each FK then create a SECOND query including your 'main' table and the query you've just written, link the table and query together using the PK/FK (primary key/foreign key) link.

[June7]

or build a report and use report Sorting & Grouping features with aggregate calcs in group footer sections.