What is the structure of your table? Perhaps something like the
following?
ServicesReceived
PersonId (Number field)
ServiceCode (Number Field)
And can the same person get the same service more than once?
If so, I think you are going to have to de-normalize the information with
a query and then run a totals query against that to return the results.
Your other option would be to run a series of queries and populate a work
table with the information or use the series of queries in a UNION query.
UNION QUERY would look like the following (and will only work with a
limited number of options.
SELECT PersonID, "12" as ServiceList
FROM ServicesReceived
WHERE ServiceCode = 12 AND
NOT Exists
(SELECT * FROM ServicesRecieved as T
WHERE T.ServiceCode Not IN (12)
AND T.PersonID = ServicesReceived.PersonID)
UNION ALL
SELECT PersonID, "12, 13" as ServiceList
FROM ServicesReceived
WHERE ServiceCode = 12 AND
NOT Exists
(SELECT * FROM ServicesRecieved as T
WHERE T.ServiceCode Not IN (12,13)
AND T.PersonID = ServicesReceived.PersonID)
You could use a series of queries like the above to populate the work
table and base your count on the work query.
Other option uses a custom VBA Concatenate function (see URL below). My
sample uses Duane Hookom's
SELECT PersonID
, Concatenate("SELECT Distinct ServiceCode FROM ServicesReceived WHERE
PersonId=" & [PersonID] & " ORDER BY ServiceCode",":") as ServiceList
FROM [PersonTable]
Now use that query to get your counts
SELECT ServiceList, Count(PersonID)
FROM qConcatServics
GROUP BY ServiceList
Here are links (url) to three example concatenate functions.
Duane Hookom
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16
Allen Browne
http://allenbrowne.com/func-concat.html
The Access Web
http://www.mvps.org/access/modules/mdl0004.htm
John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
Hi Folks - We track services to people. The services have number codes
like 12, 13, 14, etc. I have a table of data that lists the client and
their service codes. I need to report on service usage. For instance, I
need to track the number of people that use service 13 only, service 13
and 14, service 13 or 14, service 15 only, etc.
I'm trying to design a query that can display the above results. I can
easily add criteria, but I don't know how to set the criteria so that I
can display the above desired combinations. Ideas? Thanks.
Mike
