Calculating percentages of non numerical data

K

Kelly

I have a database that works with opened and closed client cases. Each
client is assigned a specialist. I need to query for the number of closed
cases in a year and the total number of closed cases for each specialist. I
then need to calculate the percentage of closed cases for each specialist.
Is there someone I can calculate the percentage of closed cases for each
specialist in a year? I need to then put this information into a report.
Thank you for any help you can give me.
 
O

Ofer

I would create two queries
1. return the sun of close case per year
2. link the table to the query above by the year number, and gives the
amount of case per specialist, with another field that calculate the procent
per specialist

If you need help with the queries I will need the name of the table, and the
fields names
 
K

Ken Sheridan

Assuming Closed is a Boolean (Yes/No) column you can use the IIF function to
return a 1 or 0 for Closed (True) or Open (False) and SUM the return values
to give you a count, grouping the query by year (using the YEAR function if
each row has a true date field). The total Closed cases can be obtained by
an ungrouped subquery on the table correlated with the outer query on the
year. The percentage is then just a simple arithmetical calculation, e.g.

SELECT
YEAR(YourDateField), AS TheYear, Specialist,
SUM(IIF(Closed,1,0)) AS SpecialistClosedCount,
(SELECT SUM(IIF(Closed,1,0))
FROM YourTable AS S2
WHERE YEAR(S2.YourDateField) = Year(S1.YourDateField))
AS TotalClosedCount,
SpecialistClosedCount/TotalClosedCount * 100 As PercentageOfTotal
FROM YourTable AS S1
GROUP BY YEAR(YourDateField), Specialist;

If you have an 'Open' Boolean column rather than a 'Closed' one you simply
have to reverse the IIF function's second and third arguments:

IIF(Open,0,1)

All you then have to do is base a report on the query. If you want it for a
specific year you can of couse restrict the outeer query to this by means of
a WHERE clause, or you can leave the query as above and filter the report to
a particular year by opening it with:

DoCmd.OpenReport "YourReport", WhereCondition:= "The Year = 2005"
 
K

Kelly

Ok I do need some more help, thanks for responding by the way. My table is
called tbl_Main and the fields are Assigned Specialist, Closed Date, 1st
Closed Date, 2nd Closed Date (I have several closed dates because some client
cases are reopened and closed several times. thanks
 
O

Ofer

Is there a limit to how many time a case can be open and closed?
Why you are not keeping the cases in different column rather then in the
same record but in seperate fields?
If a case is closed in 2004 and then reopen in 2005, does it need to be
counted in both years?
If the same case was reopen few time during the year does it need to be
counted all this times?

I think it is better to save each case in a different record, you can't keep
on adding new fields to the table.
 
K

Kelly

There is not limit to the number of times a case can be reopened, but it is
very unlikely that it will happen more than three times. Cases rarely (very
rarely) are open and closed within the same year. Most cases are open for
several years before they are closed again. We keep all information in the
same record because each client is assigned a specific ID number that we use
to track them throughout the process so we can't have seperate records each
time they are reopened. Is there any possiblity of me doing this query with
the information I have?
thanks
 
O

Ofer

Would you consider changing the table, to include to fields, specialist and
closed date, and then adding a new record every time there is a new closed
date.

If its to late to so, then will try and do it, but its going to include few
queries or code
 
K

Kelly

Unfortunatly, that isn't possible. The table was already set before I
started the job. I have no choice but to work with it the way it is.
 
O

Ofer

If you would like to post me an mdb that include the table only. you can
remove most of the records.
Send it to [email protected]

change the extention of the mdb to bmp
DBName.mdb to DBName.bmp hotmail will remove mdb's
 
Top