Help Please! Need to set up query

P

Pam

I have set up a query that tells me how many mediations my office has each
week. I have a field for "status" of the mediation. I'd like to be able to
calculate the percentage of mediations I have in any given month has a
"status" of "settled". I can't figure out how to calculate the percentage.
Please help. Thanks.
 
J

John Spencer

Field and table names sure help. In its simplest form, you seem to be
asking for the following query.

SELECT Abs(Sum(Status = "Settled"))/Count(MeditationDate) as PercentSettled
FROM SomeTable
WHERE MediationDate Between #1/1/2008# and #1/31/2008#

If you want multiple months at once, you would need something like the
following

SELECT Format(MediationDate,"yyyy-mm") as TheMonth,
Abs(Sum(Status = "Settled"))/Count(MeditationDate) as PercentSettled
FROM SomeTable
WHERE MediationDate Between #1/1/2005# and #12/31/2007#
GROUP BY Format(MediationDate,"yyyy-mm")

If you can't work out a solution from the above, post the field names and
tablenames. If you don't know how to build a query in the SQL view, but
must use the design view (query grid) then post back and I will try to lead
you through the process.


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