Average Time Query

[GJT]

I have a report schedule that displays various information for our production dept. The underlying query for this report returns a number of fields from a handful of tables.

I now need to add an additional couple of fields to this report that will detail the appoximate time to complete the job.

In a nutshell, the report has a product code identifier - this identifier specifies a particular component size. I have written a query that returns the top 5 records from a SIZEDATA table (for this identifier) and a secondary query AVG_TIME that calculates the average of these times. This 'Average' information I need returned and displayed on the report. I have tried using a sub-form (whose control source is AVG_TIME - and the parent - child links specify the identifier as detailed above) but this does not seem to be working properly.

Can you identify where I could be going wrong or is there a simpler way of constructing such a query?

Regards

GJT

 

[namliam]

Howabout if you use a SUB REPORT instead of a FORM?

Regards

 

[GJT]

Sub form / report - either should work but to no avail.

I think the problem is that I am trying to generate an average from the TOP 10 values 'on-the-fly'. Maybe this cannot be done easily...????

I had thought of doing a table that is generated every once in a while that contains all sizes and their associated average manufacturing times. My main reporting function would then retrieve the appropriate time according to size. This of course has the draw back that there is then a reliance on the administrator to re-run the query periodically to generate fresh averages from any new manufacturing data that has been added onto the system.

So here it is in summary :

1. Main report runs to return the contract data
Sub-report then initiates the following :
a) (knowing the component ID from Main Report) - retrieve the TOP 10 most recent manufacturing times for this size
b) generate the average time
c) populate sub report field with AVG value

I trust someone out there can help...



Guy