Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2013

    Unanswered: Nested DCount Function not working properly

    This is my first post. I will try my best to explain without confusing. I have a table with a list of records, each record has a group, there are 10 seperate random # generated columns. Another table has a list of the groups and counts or number of records that need to be labeled as test or control out of each group. I can get the fuction to work if it is only labeling the control records. When I tried to nest in the function to include the test counts, it does label some of them but not all. Note: this query takes forever to run, so if you have any other suggestions, I'm all ears.

    Working Function
    IIf(DCount("[TEMP].[COMB]","TEMP","[TEMP].[COMB] = '" & [TEMP].[COMB] & "'")-DCount("[TEMP].[COMB]","TEMP ","[TEMP].[COMB] = '" & [TEMP].[COMB] & "' AND [RAN1] > " & [RAN1])<=[TEMP COUNTS].[CNT_CTRL],"CTRL","")

    Does not work:
    IIf(DCount("[TEMP].[COMB]","TEMP","[TEMP].[COMB] = '" & [TEMP].[COMB] & "'")-DCount("[TEMP].[COMB]","TEMP ","[TEMP].[COMB] = '" & [TEMP].[COMB] & "' AND [RAN1] > " & [RAN1])<=[TEMP COUNTS].[CNT_CTRL],"CTRL",IIf(DCount("[TEMP].[COMB]","TEMP","[TEMP].[COMB] = '" & [TEMP].[COMB] & "'")-DCount("[TEMP].[COMB]","TEMP ","[TEMP].[COMB] = '" & [TEMP].[COMB] & "' AND [RAN1] > " & [RAN1])<=[TEMP COUNTS].[CNT_TEST],"TEST",""))

  2. #2
    Join Date
    Jan 2005
    Running aggregate functions requires Access to check through every record in the table each time the function is called, hence the slow speed. Instead, create a subquery that performs your counting and link that to the table in your query your basing things off of. That will also make it easier to see your syntax.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts