I have two look-up fields in my form and I would like to count the instances one of the options is selected. For example, I have Outcome field with "Positive", "Negative", "Unknown" options and I would like to count how many are "Positive". My progress so far: 1. I found how to do this for check boxes (yes/no fields) so I could manage those ones at least. But I do not want to turn these into yes/no fields because I intend to filter and follow up with the "Unknown"s later. 2. I managed to create a query that tells me how many times each of the three options were used (although for some reason it chose to start with a nameless row with 0 count, see screenshot). Could this be somehow something I can use for the form total field?
That being said, the reason for the blank is you have an Outcome with nothing selected.
This is a simple fix. In the criteria for the Group By column enter Is Not Null .
Sedef IskitOP
@Reply 3 years ago
Thanks for the quick response Kevin. I know lookup fields are inconvenient but still, is there a way to count a specific option in the scenario I am working with right now?
Kevin Robertson
@Reply 3 years ago
You could use a DLookup to get a count from the query you have already built or you could bypass the query altogether and use DCount directly in the Form Footer Fields. My screenshot below illustrates this method.
Kevin Robertson
@Reply 3 years ago
Sedef IskitOP
@Reply 3 years ago
Thank you Kevin. In the meanwhile I progressed to mid-Expert 7 and got a better idea of why we do not want Look-up fields and I am working on my junction tables. But it is always good to learn a new function :)
Sorry, only students may add comments.
Click here for more
information on how you can set up an account.
If you are a Visitor, go ahead and post your reply as a
new comment, and we'll move it here for you
once it's approved. Be sure to use the same name and email address.
This thread is now CLOSED. If you wish to comment, start a NEW discussion in
Access Expert 3.
