Results 1 to 13 of 13
  1. #1
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83

    How do I use an expression result as my criteria to count


    I suspect this is really simple, but it has me stumped. Here is my query:

    Click image for larger version. 

Name:	CountQuery.jpg 
Views:	19 
Size:	178.2 KB 
ID:	25229

    I want to use the output of the Expression [TenthIC50] as the criteria to restrict the record count of [AvgIC50]. What is the correct procedure/syntax? I've tried both Count and DCount and it seems that Access doesn't recognize [TenthIC50].

    Thanks for helping!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Using [TenthIC50] could be the problem,since it could be a lot of values. Say 1 value is A and another is B.
    when the query hits letter A, then B is a legal value because its not A., and so on.
    SO, you don't use this method, you must use the IN method, and say that field is not IN that table.

    use the query wizard and create a Find Duplicates Qry. Then look at the SQL. That is how to use the IN operator.
    and you want NOT IN.

  3. #3
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Thanks Ranman, but I don't quite follow. The first count works perfectly and is counting the number of records where the criteria has been set to the Max value of the field where the values 10,000 and 1,000 are excluded. Now I've simply taken a fraction of the max value and want to apply that as the filter for the next count. The problem I'm encountering every time is this:

    Click image for larger version. 

Name:	CountError.jpg 
Views:	19 
Size:	142.2 KB 
ID:	25230

    This time I'm showing the DCount. I have all of this working in a form, but I want to compile the data with this query so I can perform additional calculations.

    Edit: Everything is probably too small, but the warning message states that the name [TenthIC50] cannot be found.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I have had problems using a column in a query that is an expression (like [TenthIC50]) in another calculation (TenthCount). Apparently Access does not recognize the name of the expression/calculation in the other calculation.

    You have something like:
    Code:
    TenthCount: DCount(field, domain, "AvgIC50 <= " & [TenthIC50]
    Try changing it to
    Code:
    TenthCount: DCount(field, domain, "AvgIC50 <= " & ([mIC50] * 0.1)
    I couldn't read the expression in blue from the attached picture..... Hope you get the idea.

    --------------------------------

    Another way is to save the query "qryTKSAcba" (I think that is the name of the query) without the column "TenthCount".
    Create a new query, adding the query "qryTKSAcba" and dragging down all of the fields.
    Now add the new column
    Code:
    TenthCount: DCount(field, domain, "AvgIC50 <= " & [TenthIC50]
    Because "[TenthIC50]" is now a "field" in the saved query "qryTKSAcba", the "TenthCount" should now work.

  5. #5
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    I had already tried to change the reference to ([mIC50]*0.1) and that didn't work. But I'll try again in case I missed critical punctuation somewhere. I did notice that the problem seemed related to not having a table to refer to and considered creating a linked table. Your idea of the referenced query is better as I need to run these calculations regularly moving forward.

    Somewhat relieved and concerned that it's actually as hard as it seems to be. I thought I was just dumber than I know I am (or so the voices say). Has anyone ever stroked out trying to do this crap? Sometimes really infuriating!

    Me and the voices will ultimately figure it out and post our solution - unless of course some kind soul wanders in and has mercy on poor pitiful me.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I've had mixed results using one calculated field in another, but you'll never be able to use the alias in the criteria because the WHERE clause is evaluated before the SELECT clause, thus the alias is unknown at that point. I would expect using the calculation to work.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Okay. So I now understand a bit better - and the wonderful news is that everyone is right (isn't that wonderful Tiny Tim?)! Turns out what I need to do is run a sub-query, and I suspect a nested one at that. That's what Ranman was trying to tell me when he said to use an IN/NOT IN operator, and Steve was on the right track with running one query and using it for the next.

    Really this Access tricky one is....

    So now I'm off to the drawing boards. I have a pea brain to work with and I suspect this will take me a bit of time. Back with the results, well, when I'm done I suppose.

    Too-da-loo {waves goodbye and skips away like a proud school girl}

  8. #8
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Alright, the schoolgirl is back. Her mascara is running down her face and her panties are all bunched up ...sniff, sniff.

    So I thought I would start simple and revise my existing expression to instead incorporate a subquery. For some reason I find it simpler to work in SQL - here's what I started with that works great:

    Code:
    SELECT tblCompRefLog.CompRefID, Count(tblDataCBA.AvgIC50) AS Inhibited
    FROM tblCompRefLog INNER JOIN (tblCompRefInv INNER JOIN (tblConstructs INNER JOIN (tblCellAssayLog INNER JOIN tblDataCBA ON tblCellAssayLog.cellassayPK = tblDataCBA.cellassayFK) ON tblConstructs.constructPK = tblCellAssayLog.constructFK) ON tblCompRefInv.comprefinvPK = tblDataCBA.comprefinvFK) ON tblCompRefLog.comprefPK = tblCompRefInv.comprefFK
    WHERE (((tblDataCBA.AvgIC50)<>10000 And (tblDataCBA.AvgIC50)<>3162 And (tblDataCBA.AvgIC50)<>1000))
    GROUP BY tblCompRefLog.CompRefID
    ORDER BY tblCompRefLog.CompRefID;
    And here's what my pea brain came up with:

    Code:
    SELECT tblCompRefLog.CompRefID, Count(tblDataCBA.AvgIC50) AS Inhibited
    FROM tblCompRefLog INNER JOIN (tblCompRefInv INNER JOIN (tblConstructs INNER JOIN (tblCellAssayLog INNER JOIN tblDataCBA ON tblCellAssayLog.cellassayPK = tblDataCBA.cellassayFK) ON tblConstructs.constructPK = tblCellAssayLog.constructFK) ON tblCompRefInv.comprefinvPK = tblDataCBA.comprefinvFK) ON tblCompRefLog.comprefPK = tblCompRefInv.comprefFK
    WHERE tblDataCBA.AvgIC50 NOT IN (SELECT [AvgIC50] FROM [tblDataCBA] WHERE [AvgIC50] = 10000 AND [AvgIC50] = 3162 AND [AvgIC50] = 1000)
    GROUP BY tblCompRefLog.CompRefID
    ORDER BY tblCompRefLog.CompRefID;
    The latter took maybe 20 seconds to run, and when it was done it simply counted all the records without even applying my filter. So I suppose I have much more to do before I have a handle on this. Please feel free to help me expedite my learning by pointing out my dumb mistake(s).

  9. #9
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Alright. I discovered the problem. I cannot have more than one criteria in my subquery - it runs "fine" if I limit the criteria to WHERE [AvgIC50] = 10000. But I say "fine" because it is still miserably slow. I think a faster solution will be to establish a table with all of my cut-off values for each compound, and then use that table to reference the criteria for my counts. Somewhere in my muddled intellect it seems like this should work, which means it likely will not.

    Off to bed to have sweet nightmares about this.

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Great reference on subqueries
    http://allenbrowne.com/subquery-01.html
    http://allenbrowne.com/subquery-02.html

    I cannot have more than one criteria in my subquery
    Hmmm... ??

  11. #11
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Quote Originally Posted by orange View Post
    Thanks for the references - will check those out right now. Should have been more specific regarding the subquery. I had to remove the AND references to 3162 and 1000. If I leave those in, there's no filter applied. Seems strange, but must be a limitation of the subquery?

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  13. #13
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    First reference EXTREMELY helpful. My biggest issue is that I have never learned any computer programming languages, so constructing the SQL for these subqueries....well, it makes me head spin and spin and get very sore :-(

    Practice covers imperfections though :-)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 17
    Last Post: 12-24-2014, 10:08 AM
  2. Expression with mixed value result
    By chelseasikoebs in forum Access
    Replies: 2
    Last Post: 10-23-2013, 09:18 AM
  3. Replies: 5
    Last Post: 09-25-2013, 09:35 AM
  4. Replies: 7
    Last Post: 06-28-2013, 12:15 PM
  5. Replies: 3
    Last Post: 07-12-2010, 01:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums