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.
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","")
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.