Filtering a COUNT

[rbennion]

Guys,



Why am I struggling with this the way I am.

I have a list of rehab patients. All patients are "referred" however only some "enroll", meaning actually show up. I just need to COUNT the amount that actually enroll (I identify these by if they have an enrollment date or if it's just NULL). Basically, I just need to:

COUNT(Enrollment_Date)
FROM General
WHERE Enrollment_Date IS NOT NULL

Obviously, this wont work. I have used IIf but I really don't understand it and feel like it's just a work-around. Should I be using a subquery? Should I use Switch? I would love your recommendation. If it is one of these I have just mentioned, please elaborate a little as I rarely use either subquerys or switch (I used to just use CASE but Access queries doesn't allow it).

Thanks all!

[ranman256]

dont need the IIf .
make a query, Q1 to pull all EnrollDate not null. or other criteria.
make Q2 to count the recs in Q1.

[June7]

Assuming each patient can have only 1 enrollment record, should be doable in 1 query.

SELECT Count(*) AS CntEnrolled FROM General WHERE NOT Enrollment_Date IS NULL;

If you want to count by month or year include those values in grouping.

SELECT Year(Enrollment_Date) AS Yr, Count(*) AS CntEnrolled FROM General WHERE NOT Enrollment_Date IS NULL GROUP BY Year(Enrollment_Date);

[NoellaG]

Hi ,

I don't see why the expression is not null shouldn' work. I just tried the following in my DB:

select count(ID) from Customers where ModifiedDate is null : yielded 532 records
select count(ID) from Customers where ModifiedDate is not null : yielded 2714 records
select count(ID) from Customers : yielded 3246 records

This seems OK to me