Apply filtered Max Expression to subform?

[Accessed]

I have the following form:





The main form has the (blacked out) combo box that lets me specify a variable (63 options) that is applied to the subform control "sfmActivityCB". This allows me to evaluate data for each combo variable on the fly. Within the footer of the main form (frmActivityCB) I have placed unbound text boxes for which I would like to display select data derived from the AvgIC50 [IC50 (nM)] field of "sfmActvityCB". These data are then referenced using another text box in the main form as shown. As you (can't) see, the count function was used to return the data for "Assays tested":

Count([AvgIC50])

That's the easy part. But then it gets tricky because for the remaining evaluations I need to exclude those data with (exact) numerical values of either 10,000, 3,162 or 1,000. As I understand, the DMax and DCount functions cannot be applied to a subform, and in my case this may be particularly true because I have built the requisite queries within the form rather than as stand-alone entities. So, to calculate the data for "Assays Inhibited" I used a set of nested IIF expressions as follows:

Count(IIf([AvgIC50]<>10000,IIf([AvgIC50]<>1000,IIf([AvgIC50]<>3162,1,Null),Null),Null))

This works great, though it feels clumsy. Then comes the next hurdle that I have been unable to solve. I want to display the max value for [AvgIC50] in the text box "Highest IC50", but must filter out the same three exact values first (10,000, 3,162 and 1,000). I cannot visualize a way to do this using the Max expression. Any ideas from the clever folks using this forum? If there is an easier way to do all of this, please let me know. I can post the database, but will need to do some work on it because it contains a fair amount of sensitive/proprietary information.

Thanks in advance for helping.

[NTC]

I do not fully follow your post. But I will contribute my view that complex math is best performed in queries rather than the form object level. What you have achieved so far at the form level is definitely good - but one can hit the wall with this approach and that may be the case here.

[Bulzie]

Not sure why you can't use those Functions in a subform, maybe try it and see?

[Accessed]

I really appreciate the responses and the desire to assist. If anything I think I've learned that what I want to do is getting complicated. First, let me show the bigger picture:



So, what I left out before is that the main form actually has three subforms. Each of these three subforms are form objects that are based on the active record (combo box control named cboSelectCompound that presently lists "Crizotinib") that is within the main form. That form is based on the primary table containing each of 63 compounds. I know this is already getting confusing again, so I'll stop at this point.

My objective: Where you see the big black box is a list of essentially random numbers that are sorted in ascending order. At some point these numbers = 10,000. For reasons too complex to describe, I want Access to ignore any number = 10,000 and only return the maximum value of the highest number that isn't 10,000. In addition, I only want it to perform this action on the data that are in the subform.

Here's some of what I've learned so far:

1. I can save the query that I wrote within the form with it's own name (qryCompoundCB) and it is added to my list of queries (and now referenced in the subform).

2. I can use DMax where my expression is the field [AvgIC50] and the domain is the query "qryCompoundCB".

3. I can apply criteria "[AvgIC50]<>10000 and the highest number that isn't 10,000 is returned.

So you're done then you say, right? Well, not quite. You see, the value returned is for all values in the query rather than those values linked to the one specific variable within my collection of 63. This does me little good in this desired application.

So I suppose the simplest fix is to use Dmax where I apply multiple criteria such that not only do I filter out 10,000, but also specify the compound (1 of 63 variables) for which I want the maximum value returned (using the active form record in the combo box (cboSelectCompound). This though is tremendously unsatisfying as it seems to me it's wasted CPU processes to query the active control, present the desired data in my subform, and then scan the entire query to pull out the data I want.

For God's sake - the data I want is right there in my subform, so why can't I just tell my computer "Janice (that's not her real name), please exclude the number 10,000 from those subform data and tell me what the highest number is".

How hard is that?

P.S. I also learned that I can actually use the query as my object in the subform. But when I do that and then ask my text box to return Dmax for the same query as the domain, all hell breaks loose - the text box simply starts flashing and I have to go to the task manager to stop Access. Guessing I create some sort of loop where Access is going back and forth between the subform and the actual query. Don't do this at home kids!

[Accessed]

I've had an afterthought that I thought I would share separately. It occurred to me that I should be able to use the same nested Iff approach described above to set the offending values to zero. In this way the desired max value should be returned. If I don't suffer terminal insanity trying to create the collection of nested arguments, I'll be back to report the result.

[Accessed]

Unfortunately, foiled again!

This expression:

=IIf([AvgIC50]=10000,"0",Max([AvgIC50]))

Simply gave me this:



The dreaded 10K! (P.S. I also used it without quotes around the zero).

But I'm actually showing these data in part because no one will care (or find them particularly useful), but also because they perhaps show another possible approach. As you can see, there are three assays inhibited (values less than 10K). From this, perhaps I can ask Access to kindly report the IC50 (nM) ([AvgIC50] field) value for that third record? Seems this would be a much more elegant solution to my problem. Hints welcomed and I'll watch my email, but it's back to Google I go.

[orange]

Hello again,

In clear steps describe what you want in plain English. Focus on the calculation re

the data I want is right there in my subform,

Then show the query that you are referring to.

1. I can save the query that I wrote within the form with it's own name (qryCompoundCB) and it is added to my list of queries (and now referenced in the subform).

Then let's work through some arithmetic and see what happens.

In theory,
Select myField from myTable where myField <10000 should get you the values below 10000

you can constrain that further based on the selection of the 1 in 63....

Good luck.

PS: You've done a lot of work --good stuff.

[Accessed]

Greetings Agent Orange!

Thank you for the compliment - these Access Databases can be tricky, and they're both rewarding and endlessly frustrating. So let's start with plain English:

Please examine the subform object named "sfmCompoundCB"

Find the maximum value, but ignore the number ten-thousand.

Display that value in the text box named "txtMaxIC50"

So, here is a snapshot of the query builder for "qryCompoundCB". You can see I've applied the criteria to ignore 10,000:



If we now go back to the main form, we have this:



As we would expect, the value 10,000 is no longer shown. And herein lies the problem - that number must be shown for this application! You can see the net effect of placing the criteria within the query - now the number of assays tested is listed as 3 rather than 14 as we had when no criteria was applied to the query. You can also see the highest IC50 number is incorrect - that's because the expression being used within the text box is:

=DMax("[AvgIC50]","qryCompoundCB","[AvgIC50]<>10000")

As is being asked of the expression, it is applying the specified criteria to the query and returning the correct result, which is the highest IC50 that isn't 10,000. But it's looking in the entire database of some 7,500+ records.

So, I have one of two problems that I have to solve:

1. Further constrain the DMax expression to include a query of the active control (cboSelectCompound) in the main form (not sure how to do this in an expression).

2. Figure out the correct syntax to tell DMax to look within the subform. Everything I have tried to name the subform as the domain has issued errors. But ultimately I want the main form to look like this:



But, with the highest IC50 value correctly displaying 526.1.

Hopefully this is clearer than mud?

[orange]

Off to play golf but will look at it later.

What is the recordsource for the subform? And what are the link fields?

I just see output of "sfmCompoundCB"

[Bulzie]

So my understanding is you want to find the max value of the IC50 for all records that have a specific compound value(your combo box) and do not equal 10000? So based on that assumption, in a query add the IC50 field twice and the compound field(not the select Compound at top but the on in your query). Use the Totals(grouping) button and in the Totals row, select "Max" on the first IC50 field, on the 2nd IC50 field use "Expression" and in the criteria put <> 10000, on the compound field use "Expression" and for criteria point to that Combo box at top (use query builder and get correct syntax to point to that field, will be like Forms![frmMain]![SelectCompound] ). This should return the highest value in that field excluding all 10000 records.

[Accessed]

Problem solved:



How I did it. First, I placed a new text box in the footer of the subform (txtCompRef). That text box references the combo box with the compound PK - I also referenced that value on the main form as shown simply for monitoring/troubleshooting purposes.

I then modified my DMax formula as follows:

=DMax("[AvgIC50]","qryCompoundCB","[AvgIC50]<>10000 And [comprefPK]=[txtCompRef]")

Seems to be working great so far!

[orange]

Good stuff!
Continued success with your project.

[Accessed]

Here's where I'm now at post-macro disaster (see the other post from today). Rather pleased with how this has progressed :-) (I know...pride and deadly sins and all).



The three sets of twin text boxes will lose their rightmost member in the production model. They're just their to help me track and validate the build. Thanks to everyone for suggestions and the help that has made it possible for me (and the voices) to get this far :-)

[ssanfu]

Good job... you have really progressed on this...


I know the voices aren't real, but their ideas are sometimes awesome!

[Accessed]

Thanks Steve - mucho appreciated :-) BTW - should you happen to have time, could you (or the Agent of all things Orange) take a look at my other post about building an object that will let me visualize the TKSI for all compounds simultaneously? I think I've blown out my Cerebral Cortex and there's nothing left upstairs but...

Cobwebs and Voices.