Counting Combination Services
in [Queries]
|
From: Michael on 9 Apr 2010 08:20 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
From: John Spencer on 9 Apr 2010 09:28 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 Michael wrote: > 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 > >
From: Michael on 9 Apr 2010 10:02 Thanks for the suggestions ... Mike "John Spencer" <spencer(a)chpdm.edu> wrote in message news:uC5d$h%231KHA.260(a)TK2MSFTNGP05.phx.gbl... > 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 > > Michael wrote: >> 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 >>
|
Pages: 1 Prev: Finding data prefixed with '??' Next: Query for previous week, Sun thru Sat, from date field |