How to find client who has been provided service in more than one category

[johnny_swindle]

I am attempting to help a non-profit organization with their access database. The question is: how to count the number of clients who have receive services in more than one category during a specific time frame.

client table: holds demographic data; cID if primary key, autonumber
service table: holds services that have been issued to the clients: serviceID primary key, autonumber, cID foreign key linking to client table

Let's call the service categories Cat1, Cat2, Cat3, etc. that relates to a specific administrative category of the item that the patient received.
Finding the client and all of the categories within a time frame is not a problem. However, I am not able to think of a way to pull out the client(s) that have gotten service in say Cat1, and Cat2 and be able to count the number of clients.

Hopefully my explanation is somewhat clear.

Johnny

 

[The_Doc_Man]

As an observation, I don't think that table description can be correct for the goals you stated.

If the serviceID is a primary key then you can only have one cID link. If another client comes along to use that service, the PK stops it.

In your description, I would think the autonumber field perhaps should be your prime key. Oh, you COULD have non-primary keys on either serviceID or cID fields, just not unique keys. Then you could generate queries where you GROUP BY cID to see how many serviceID each user has, or GROUP BY serviceID to see how many client (cID) you have.

 

[johnny_swindle]

As an observation, I don't think that table description can be correct for the goals you stated.

If the serviceID is a primary key then you can only have one cID link. If another client comes along to use that service, the PK stops it.

In your description, I would think the autonumber field perhaps should be your prime key. Oh, you COULD have non-primary keys on either serviceID or cID fields, just not unique keys. Then you could generate queries where you GROUP BY cID to see how many serviceID each user has, or GROUP BY serviceID to see how many client (cID) you have.

Thank you for your reply. There is one to many relationship between client and service table. If the current structure of the two tables prevents creating a query/series of queries to do the job, the a little manual count may be necessary. Restructuring the database and relationships is no feasible at this time.

 

[GaP42]

@johnny_swindle - can you
1. post a view of the relationships involving Client, Service and ServiceCategory
2. post the Design/ SQL you already have for the client and all of the categories within a time frame

 

[plog]

No where in your table descriptions do you list how the category is involved. So I will help in general--you need a subquery. That subquery would group all the clients and categories they received. The SQL would look something like this:

SELECT cID, Category
FROM tblServices
GROUP BY cID, Category

Let's call that 'sub1'. That gets all the unique client/category permutations regardless of times a specific category occurs for a client. Next, you build a query on sub1 and exclude clients with just 1 category:

SELECT cID
FROM sub1
GROUP BY cID
HAVING COUNT(cID)>1

That query give syou all the clients with services in more than one category.

 

[johnny_swindle]

No where in your table descriptions do you list how the category is involved. So I will help in general--you need a subquery. That subquery would group all the clients and categories they received. The SQL would look something like this:

SELECT cID, Category
FROM tblServices
GROUP BY cID, Category

Let's call that 'sub1'. That gets all the unique client/category permutations regardless of times a specific category occurs for a client. Next, you build a query on sub1 and exclude clients with just 1 category:

SELECT cID
FROM sub1
GROUP BY cID
HAVING COUNT(cID)>1

That query give syou all the clients with services in more than one category.

Thank you plog....
I took you example and added date range and other parameters that were needed and it displayed what was needed.

Again, thank you. You don't know how much you helped.

Johnny