How to sum checkboxes, but just once per name? (1 Viewer)

chacham

Member
Local time
Today, 02:00
Joined
Oct 26, 2020
Messages
45
A form lists names with some data and three checkboxes. I've been asked to add totals to the footer for each of the checkbox columns. 2 of them are to display total unchecked for records that have a date. (It is not supposed to be able to be checked if there is no date.) The third checkbox is to sum without checking date, but is to be unique per name. That is, if the name is listed twice, we only count an unchecked checkbox once. (My understanding is that this checkbox is per-patient, not per-record).

For the two checkboxes, this seems to work: =Sum(IIf([date] Is Not Null And [checkbox] = 0,1,0))

For the other checkbox, is there a simple formula to make that sum unique? Or should i go back to the query again with a count distinct?

(Edit: Looks like COUNT(DISTINCT) is not supported. Meaning, it's either a COUNT(*)/GROUP BY or COUNT(*) FROM (SELECT DISTINCT))
 
Last edited:

Ranman256

Well-known member
Local time
Today, 02:00
Joined
Apr 9, 2015
Messages
4,339
in a formula, use IsNull(txtBox)
for you:
=Sum(IIf(not isnull([date]) And [checkbox] = 0,1,0)) 'if checkbox = false
 

chacham

Member
Local time
Today, 02:00
Joined
Oct 26, 2020
Messages
45
It's better to use isnull(x) over just plain x is null?
 

chacham

Member
Local time
Today, 02:00
Joined
Oct 26, 2020
Messages
45
To answer my own question, though i do not know if it is best, i added a another query to query the form's query:
SQL:
SELECT COUNT(*) AS Total
FROM (SELECT DISTINCT <Name> FROM <First Query> WHERE <Checkbox> = 0);
The textbox then uses DlookUp():
Code:
=DLookUp("Total","<Second Query>")
 
Last edited:

Users who are viewing this thread

Top Bottom