Using Count In Query

[rcorbett]

I am trying to constuct a query that will count multiple fields that fall within a date range. You would think this would be easy but I seem to have forgotten query basics. I have a date received field (will set to be prompted), I want to count blood types for those who are a patient or donor - and then add them together that fall within a date range. Here is my unique dilema to this...If someone is a donor and the field titled other is marked too then I don't count them. I can't figure out how I'm going to do that one unless it is just subtracting those. I would appreciate any thoughts - thanks.

 

[Golom]

Here's a short COUNT primer. The Count(..) function returns two possible values. "Count(*)" returns the number of records including records that contain NULLS. "Count([fieldname])" returns the number of records where "[FieldName]" is not NULL. Neither meets your requirement. You want "... the number of occurences where a specific condition exists..."

For that you need a construct like
[tt]
SUM ( IIF ( <Condition>, 1, 0 ))
[/tt]
That will count the number of records where your <condition> evaluates to TRUE. From your description it looks like your <condition> will be something like
[tt]
Doner = TRUE AND Other = Marked
[/tt]
... or however you determine that someone is a donor and the "Other" field is "Marked".

 

[rcorbett]

Thanks for the input. Trying to play around with your suggestion. The fields are just Yes No values.

My dilema is to sum patient or donor in one query but to make sure to not inlcude donors who have the other field marked.

 

[nicsin]

what about using the HAVING keyword?

select
count(aField)
from
aTable
having
anotherField = aValue