Problem with Count aggregrate function and group by 1

[mimivdg2002]

I have a table that contains program_id, program_name, signed_date, paid_date. I would like to get a count of the signed_date field where the paid_date is not blank. I have the following query:

SELECT Program_ID, Count(Signed_Date)
FROM Program
GROUP BY Program_ID, Paid_date
HAVING (((Paid_date) Is Not Null));

Here is the problem. I only want it to group by program_id and not by paid_date but I'm forced to include paid_date in the group by clause by Access. If I include paid_date in the group by clause, it then gives me the count for each different program_id, different paid_date combination. How can just make it do the count based on program_id?

Thanks
M

 

[PHV]

You may try either this:
SELECT Program_ID, Count(Signed_Date)
FROM Program
WHERE Paid_date Is Not Null
GROUP BY Program_ID;
Or this:
SELECT Program_ID, Count(Paid_Date)
FROM Program
GROUP BY Program_ID;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244