Help Please! Need to set up query

  • Thread starter Thread starter Pam
  • Start date Start date
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.
 
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
..
 
Back
Top