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

    Need Help with DCount Syntax

    I have the following DCount code:



    Code:
    TenthCount: DCount("[AvgIC50]","tblDataCBA","AvgIC50 <= " & ([mIC50]*0.1))
    This code applies to this query output:

    Click image for larger version. 

Name:	QueryOutput.jpg 
Views:	13 
Size:	86.9 KB 
ID:	25249

    Which was constructed as follows:

    Click image for larger version. 

Name:	Query.jpg 
Views:	13 
Size:	262.0 KB 
ID:	25250

    The issue is that the query output includes the entire field filtered by the TenthCount expression. However, I want the count further filtered by individual Compound, so I need my expression to include reference to the primary key (comprefinvPK). However if I revise the above code to this:

    Code:
    TenthCount: DCount("[AvgIC50]","tblDataCBA","AvgIC50 <= " & ([mIC50]*0.1) And "[comprefinvPK]=[comprefinvFK]")
    Then it simply returns the record count for a subset of the AvgIC50 field (7506), which is obviously not what I'm looking for (not even sure what its calculating - I'm only showing a subset of the query output). Can someone please help me figure out this last bit of syntax? This has been dogging me for three days and I think I'm close to the end of the tunnel, but need to make it past this final bend.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Try this:

    TenthCount: DCount("[AvgIC50]","tblDataCBA","AvgIC50 <= " & ([mIC50]*0.1) & " And [comprefinvPK]= " & [comprefinvFK])


    Note: It's difficult to put the request into context without some data, or without a specific example showing the data you have and what you want the answer/result to be. Easier for readers if you show the data, a sample of the expected result --and the effort you have made that doesn't do what you need.

    I'm just looking at the syntax of your DCount.

  3. #3
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Thanks Orange - I appreciate the feedback. I always worry about being a burden with too much information provided, but I can accept that I may be having the opposite effect. I also feel really guilty because all I do is ask questions without ever providing any assistance to others - I simply have little confidence in my abilities and still struggle with a lot of basic concepts. That said...

    First, the revision produced this:

    Click image for larger version. 

Name:	Error.jpg 
Views:	11 
Size:	61.0 KB 
ID:	25251

    As for what I'm trying to accomplish, let me show you the final result that I was able to produce using one of my favorite tools, the subform.

    Click image for larger version. 

Name:	TKSIcompiled.jpg 
Views:	11 
Size:	131.2 KB 
ID:	25252

    This has exactly what I want, but I'm unable to find a way to calculate the average of the text boxes that I used to calculate the 0.1 and 0.01TKSI Data (that number 10 is just to make sure I'm referencing the text box in the form footer correctly). My ultimate goal is to calculate the average of the individual TKSI calculations for each compound. If we take Afatinib as the example, here are the critical calculations that must be performed:

    Click image for larger version. 

Name:	Calculations.jpg 
Views:	11 
Size:	47.1 KB 
ID:	25253

    After excluding data that are exactly 10,000, 3,162 or 1,000, the max is found for the remaining data. This max is used to set the cut-offs for the 0.1 and 0.01 TKSI values as shown (they are 1/10 and 1/100 of MAX IC50). These cut-offs are then used as the gating criteria to count all records belonging to a specific compound. The gated records are then divided by the records counted for all assays that are not 10,000, 3,163, or 1,000 (these are the "Assays Inhibited"). So for Afatinib, 0.1TKSI is 23/157 = 0.15.

    As you can see from earlier postings, I'm close to the goal, but not yet there. Hope this is helpful and not too confusing.

  4. #4
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    In looking through the database, I think I see one factor that may be causing complication. For our own data, I am using a primary key "comprefinvPK" that is a field within the table "tblCompRefInv". There is however a second table named "tblCompRefLog" that contains the primary key "comprefPK".

    Table "tblCompRefLog" lists generic attributes of each compound while "tblCompRefInv" is specific to the compounds we keep on site. In many cases we'll purchase a new lot of the same compound every quarter, and we need to track individual lots in our research. But for the biochemical data I am using, those are public information, so I chose to use the primary key "comprefPK" from "tblCompRefLog" for those data since I cannot associate specific lots with those data.

    Now I see that I'm not being consistent with my use of these two primary keys, which might be causing issues in the database. This goes back to my central form, where the combo box is set to comprefPK and is applied against all three assay forms.

    I think I'll randomize our proprietary data so that I can post up the database for evaluation. This will protect our most important asset but give everyone a database to reference for assistance purposes.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Don't be concerned with not offering assistance to others. You have posted good questions and have shown your work --others can take advantage of the dialog and your sample material.

    I don't understand how your version of the DCount produced a result and my revision gave such an error.

    Anyway, I seem to be in 1 or 2 states ---detail overload, or not comprehending the specifics.

    Did you put the revised Dcount directly as a replacement for your TenthCount epression?

    If the subform gave you the result you need, can you show the recordsource fo the subform, and/or any calculation you did beyond that?

    Option for proceeding:

    Isolate this issue to 1 assay/compound
    try to create an arithmetic expression to represent the calculation(s) involved. (I'm trying to simplify my learning curve to get the terminology and data straight)
    work on getting the simplest example (even though it isn't your final need)
    get it working then continue to add pieces until we get the final query working (consistently).

    If you can take the query you showed in post #1, and create a desktop database with only those tables, and post it as a zip,
    it might be the quickest way for me to try and offer more. I don't need your whole database--just enough data that's needed for this particular query.

    OOooopps! I see you were posting while I was typing.

    Yes randomize or remove/obfuscate the "proprietary-ness".

  6. #6
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Stand by Orange. I will post a database shortly that will help you and allow others to really see my madness in action. I'm on it (but the dog is begging for a walk, so that may need to be taken care of first).

  7. #7
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Okay, so I tried to scramble the data only to realize that with so many relationships already in the database I cannot modify many of the tables, and those I do scramble mess everything up so much that the database is rendered unusable and wouldn't help anyone. Let me instead take the option behind door number two. But first I want to try the formula again as I may have accidentally messes up the primary key references while understanding the above mentioned problem. So I'll try that first and see what happens.

    Time to walk the dog - back shortly.

  8. #8
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Agent Orange. Looked through the database and all the necessary tables contain all of our most proprietary information. To scramble for public consumption is almost impossible - at least within my feeble means. Instead I have (will) sent you a PM.

  9. #9
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Okay Agent Orange. As we discussed, I have addressed the issue with "comprefinvPK" and "comprefPK". While I'm pleased to have resolved that lingering concern - it didn't really change anything in the behavior of the database (as you suspected I think), and it also hasn't resolved the issue with the query. The problem remains that the formula refuses to acknowledge the presence of the primary key criteria, again leading me to believe that there is a simple issue with syntax.

    Back to Google I go bravely sporting my new training bra and a freshly painted face - the world is my oyster :-)

  10. #10
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Eureka Bones! We have success!!

    Take a look at the query now (I made it nice and big for all to see):

    Click image for larger version. 

Name:	Fixered.jpg 
Views:	9 
Size:	265.6 KB 
ID:	25256

    Do you see what's going on at the bottom left relative to the earlier query builder image? Now I have field references to both the primary and foreign keys for the compound. I think the confusion over the "comprefinvPK" and "comprefPK" had my panties in such a pinch that I couldn't see this. Once I eliminated that pesky inventory component (it's a long story), it was almost obvious what was missing - and all is well. Now that I have the TKSI in my query I can complete my task and generate the averages - hopefully with relative ease compared to everything leading up to now.

    Moral of the story - know where your keys are and don't try to be too clever.

    Oh - and never leave the house without your bra on ;-)

  11. #11
    Accessed is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    83
    Happier than poop in pig am I :-)

    Click image for larger version. 

Name:	PigPoop.jpg 
Views:	11 
Size:	115.1 KB 
ID:	25258

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

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

Similar Threads

  1. Replies: 11
    Last Post: 02-13-2016, 12:40 PM
  2. DCount Syntax Correct?
    By Huddle in forum Access
    Replies: 2
    Last Post: 06-21-2012, 03:06 PM
  3. Replies: 5
    Last Post: 12-15-2011, 11:16 AM
  4. Trouble with DCount syntax.
    By jessica.ann.meade in forum Database Design
    Replies: 9
    Last Post: 02-18-2011, 10:09 AM
  5. dcount syntax correct?
    By lbgtp in forum Reports
    Replies: 2
    Last Post: 12-29-2010, 10:01 AM

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