Need help with concept on client query...

[vangogh228]

I am attempting to query and report on service activity, but I am having trouble conceiving the parameters.

I have a table with four fields related to this query: Date, Client, Dept, Service. What I need to do, in the end, is report on how many clients were provided each service in a certain date range. If a client was provided a service more than once during the time period, they only count once for that service, and only once for that dept.

The final report should have the columns of Dept and Client Count... and within each department would be the columns of Service and Client Count. I know that the client counts are the same formula, depending on where I put them, and I do not need to show the detail within each service, just the count of clients.

I can't conceive on how to query the four fields (Date range is done through parameter criteria) to give me clients once only for each service and once only for each dept.

This is the final issue before I implement, so help is appreciated. Thanks!!!

Tom

 

[vangogh228]

RESOLVED:

In my underlying table of client activity, I established these fields:

ServiceAutoNum
Date
Department
Service
ClientNum

I established a query that allowed me to query a date range using BETWEEN in the query Date field.

Then, I set up a query based on that query that pulled Department, Service and ClientNum. I set the query Unique Record property to YES and IT WORKED!! When I run the dependent query, it asks for the date range needed to run the parent query.

Thought I would share this with anyone struggling with the same issue. If anyone knows of a better way, please point me in the right direction!

Tom