chacham
Member
- Local time
- Today, 03:53
- 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))
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: