Count Days in a log

[sbbrown9924]

I am writing a report from a table, SocialWorkLog, that logs services to patients given by social workers. I need to be able to count each day of the month that a social worker provides services. In the SocialWorkLog table there is a socialworker field and a servicedate field. During a given day the socialworker will often see between 10-15 patients per day. All I need to do is count once any day a service(s) has been provided. Any help setting this up would be great. Thanks.

 

[PHV]

A way to work around the lack of Count(Distinct ...) in JetSQL is to use an embedded view:
SELECT socialworker, Format(servicedate,'yyyy-mm') As [Month], Count(*) As CountOfDays
FROM (SELECT DISTINCT socialworker, servicedate FROM SocialWorkLog) AS D
GROUP BY socialworker, Format(servicedate,'yyyy-mm')

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

 

[sbbrown9924]

PHV;

Thanks for your assistance with this query. The query you gave me is close to what I am trying to do with a few key differences. I think I was unclear with my previous description.

I am trying to count the number of days between a time interval (usually a calendar month but it can be any time interval) specified by two form variables (datebegin and dateend)that a social worker worked and provided services. On any given day a social worker will provide several services to a variety of clients. The query you gave me counts all services given by a social worker in a calendar month.

I need this to be able to provide for a lot of descriptive statistics. Right now my days worked is incorrect becase it counts all the days in the time interval, not just the days a particular social worker works. So weekends and days off get counted too. Any ideas? Thanks - I really appreciate your expertise.