Displaying aggregate info about query in Text box

[SteveFlash]

Hi.



I working with Access 2010 and a form with criteria controls, a requery button and a subform that displays the query. I wanted to add a text box that would display some aggregate information about the query results. Like how many results were returned or what the average is in a column of numbers stuff like that.

If at all possible I was hoping to have the text box display aggregate information of the whole query but when a specific entry in the table is clicked I was hoping to have it show aggregate detailed information about that entry opposed to the whole table. So I am not sure if that changes the solution but I wanted to put it out there.

In my searches it looks like Dlookup is the way to go but I have seen a lot of people use Dlookup in very different ways so I might be wrong. Any thoughts?

[June7]

There is a whole collection of domain aggregate functions available: http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

There are also the SQL aggregate functions - just drop the D from the domain aggregates. These can be used in a textbox on form/report to summarize data of the form/report records. Whereas domain aggregate functions refer directly to table and query objects as source data.

[SteveFlash]

I am trying to average the field AvgSM which is on a table named AllMatchedScores, but I want it to average the field when it is found in query called Search. I created a textbox and it the control sources I placed:

=DAvg([AllMatchedScores]![AvgSM],[Search]![AvgSM]!)

But the textbox keeps showing "#NAME?" this is frustrating.

[June7]

=DAvg("AvgSM","Search")

The referenced link has guidance on the correct syntax.

[SteveFlash]

Thanks. Well I am getting a #ERROR now so I must be closer. It will give me an average if my domain is a query but it will not if I change it to a subform that displays a query. Does the textbox have to be refreshed after a query is ran each time?

[June7]

No, domain aggregate functions must reference table or query object, not form or report. SQL aggregate functions (Sum, Avg, etc) can be used on form or report in header/footer sections and will summarize only the records of the form/report.

=Avg(fieldname)

[SteveFlash]

I was able to get it to work now but the Average does not update when I change the criteria controls and requery the query. Like it shows the total aggregate from the query at all times but I want it to just show the average of entries for what is currently being queried based the criteria controls.

[June7]

Requery what query - the form RecordSource? Did you use Avg or DAvg? If you want to provide db for analysis, follow instructions at bottom of my post.

[SteveFlash]

Well I placed a Text box on my form and in the Control Source I put

=DAvg("AvgSM","Search")

My form has criteria controls and a requery button which updates the subform that displays the query based on the criteria controls that are selected. I wanted to populate the text box with an average amount for the field AvgSM that is displayed on the subform. The "Search" in the DAvg Function is the actual query and not the subform because when I try to use the subform I get an #ERROR.

[June7]

Is Search the RecordSource of the subform? Does Search have parameters that reference the criteria controls? Is this subform linked to record of main form? This might go a lot faster if I could analyse the db.

[SteveFlash]

I am sorry I can not post the db. Umm let me try to answer these. Search is the name of the query. Yes the criteria controls are a series of text boxes and combo boxes that I can eneter values and hen when I click the requery button the subform below displays results from the query called Search that matches the control criteria above. The subform is linked directly to the main form.

[June7]

Usually in a form/subform the filtering is applied to the main form and the related subform records synchronize to the parent record. If the main form is not bound then that is different. Why would you need to apply additional filter to the subform beyond the link to main form record? We could go in circles like this forever. Don't think I can be any more help.

[SteveFlash]

There are not additional filters on the subform it strictly displays the query in a nice viewable way on the form.

* I think I see what you mean. My subform that displays the query has no Link Master or Child fields. I created the subform just with the wizard and when it asked which fields I wanted I selected the ones to display. When I apply the criteria filters on the form they apply to the query called Search and not the subform. The subform just has text box who's control source is the field in the query to be displayed. The subform is unbound.

[June7]

So I am totally confused. If the subform is not bound then the wizard would not have asked which fields you wanted. Only bound forms have fields to be selected because bound forms have a RecordSource. Unbound subform cannot display query data because unbound form does not have RecordSource.

Did you mean the subform is not linked to main form?

If subform is bound to query Search then the subform must be requeried after criteria selected in controls. Review http://datapigtechnologies.com/flash...tomfilter.html

[SteveFlash]

That is what has me so confused. I have the subform properties window open right now and in the Link Mast and Child Fields it is blank. When I try to click the 3 dot builder thing next to the fields a window pops up that says:

"Can't build a link between unbound forms"