subform calculation.

[chivo123]

The attachment is an exame. Once you open the attachment ---> open up the faculty details form ---> and then open up the page 294.
In there, once you select a name, and then the dates it will give you the stats for that employee. I have made a query which it has one formula for Average Wrap (Average Wrap: ([Total outbound Talk Time]+[Total Inbound Talk Time])/[calls handled]).
What I want to do on that FORM TABLE, is when i choose the date for that agent, it will calculate the average for the span of dates that I have chosen.

For instance: Let's say the agent name is Test, then I will choose the date from Dec 20th-Dec 23rd, and it will return me the average of those 3 dates for that name. Let me know if there is anyway this could be done. Thanks alot!!

[June7]

No, it will not average those 3 dates. The subform container control has Master/Childs links:
Master: Agent;cbotest;cbotest1
Child: Agent;date;end date

This means the form can display only records that have both those dates. No records meet that condition and none will display. It will not display records that are between the dates. Including the dates in links does not set a range. The dates need to be applied as filter criteria in the query. Remove dates from the links.

Once you figure out the date filtering, you can average the records by setting the main form Popup No. Then in form view, click in the subform. The Totals button on the ribbon will be available. Click it and Totals row will show at bottom of the subform. Under the Wrap column select Average.

Are you aware the time calculation results in decimal day units, not in minutes?

[chivo123]

Thanks for the reply. You seem to be very smart. Do you know how could I be able to do that date filtering and displaying the average. Is there a way you can send me a link to do these things? I'm aware of the Time, but I will want to figure out this before moving to the next step.

[chivo123]

Also, is there any easier way I could be able to do this overall task?

[June7]

Not so smart, just been there!

Filtering by date can be very tricky, especially here where you have two date fields.

In Query1 under the date columns on Criteria row:
>=[Forms]![Faculty Details]![cbotest]
<=[forms]![Faculty Details]![cbotest1]

Records must meet both criteria in order to display.

Since the date criteria will be selected after the form opens it will be necessary to Requery the subform. You do that by clicking the RefreshAll button on the ribbon or with code (VBA or macro). Unfortunately, Requery/Refresh will move to the first record of main form.

Use DISTINCT keyword in the combobox RowSource so won't get duplicate date values:
SELECT DISTINCT Date FROM Table1 ORDER BY Table1.Date;

Not sure if easier way. What is the purpose/goal? Do you really need to show these calcs on form? Why not a report?

[chivo123]

Thanks alot! so once, i put these codes under my query 1:
>=[Forms]![Faculty Details]![cbotest]
<=[forms]![Faculty Details]![cbotest1]

and using distinct formula, what do i do next to take the average Wrap on span of those dates?

Thanks for being very helpful! I be leaving in 15 minutes, but I will be back tomorrow morning.

[June7]

Did you try my suggestion about Totals button in post # 2?

Note that the dates selected apply to all agents. If you move through the main form records, you will see the filter has been applied to the entire subform recordset.

[chivo123]

Do you think if I put IIF condition inside the textbox, under control source will make this work?
For instance: =IIf(INT[DateDiff("d",[Date],[End Date]), Sum([Average wrap]),0)

[chivo123]

"you can average the records by setting the main form Popup No. Then in form view, click in the subform. The Totals button on the ribbon will be available. Click it and Totals row will show at bottom of the subform. Under the Wrap column select Average."
Can you be more specific on this? I don't really understand it. Thanks again!

[June7]

You mean in a textbox in subform? Subform is in Datasheet View. If you do that in a textbox it will show on every row. The syntax is wrong and I don't think it will work anyway. Doing aggregate calcs on forms is not easy, that's what reports are for. I haven't done any projects that do aggregates on forms. I know from helping others it is challenging. I made a few attempts with your form, thought I had something at one point. I set subform to Continuous View, textbox in footer with Sum([Average Wrap]). A textbox on mainform references that textbox. Then I messed up trying to use and understand your expression and now doesn't work. Too frustrating for me, on your own.

[chivo123]

so you would suggest me to do on reports? Thanks for your suggestion though!

[June7]

More descriptions of using the Totals row in Datasheet view.
http://office.microsoft.com/en-us/ac...001233062.aspx
http://answers.microsoft.com/en-us/o...6-563fdfdadc1e