monthly stats query

[robertm600635]

I have a client database where a client's [location] and [environment]
changes several times during the course of their treatment. These two fields
use comboboxes on a form to determine where the client currently is. This
works fine except that i need a way to give monthly statistics to the state
agency that funds us. For example, I would need to produce the total number
of black and white clients that were in each location/environment for
January. I made an append query that runs every day and dumps the entire list
of clients, their location, enviroment, race, etc and dates them with the
current date. So now I need a query that can tell me what i need to know for
a given month. any suggestions?

 

[KARL DEWEY]

It sounds like you are creating a record per day for each client. That is
not really necessary as you can have a record that has date of the start and
end of the location/environment combination and then calculate the days spend
there.

 

[John Spencer]

Two query solution

SELECT DISTINCT Client, Location, Race, Environment
FROM YourTable
WHERE DateField Between #12/1/2007# and #12/31/2007#

SELECT Location, Environment, Race, Count(Client) as CountPeople
FROM qSavedQuery
GROUP BY Location, Environment, Race

If you are using the query Design view
-- Add your table to the query
-- Select Client, Location, Race, Environment, DateField
-- Uncheck show for DateField
-- Set criteria for the date field to show just the month desired
-- Click in the grey area at the top of the design view (not on the table)
-- Select View: Properties from the menu
-- Set Unique Values to YES
-- Save the query as qMonthStats

-- Open a new query
-- Select qMonthStats as the source
-- Select Client, Location, Race, Environment as fields
-- Select View: Totals from the menu
-- Change Group By to Count under Client

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..