Calculating percentages of non numerical data

[Guest]

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.

 

[Guest]

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

 

[Guest]

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"

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

If you would like to post me an mdb that include the table only. you can
remove most of the records.
Send it to (e-mail address removed)

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

 

[Guest]

Follow this steps

' Create reference to Microsoft DAO 3.6, Open code, anywhere, select
tools>reference>now add it
' Create a temp Table named CountRecords with this field: Assigned
Specialist (String), MyYeat (Long), NumOfClosedCase (double)
' This function will fill up the temp table with a count of close cases for
each Assigned Specialist per year
' To get the sum of close cases per Year you can use this query, on the temp
table
' SELECT MyYear, Sum(NumOfClosedCase) AS SumNumOfClosedCase FROM
CountRecords GROUP BY MyYear

' I didnt run the function, there was no data available, I hope it will run
and gives you the right result

Function CountCases()
Dim MyDB As Database
Dim MyCases As Recordset, MyCaseCount As Recordset

Set MyDB = CodeDb
Set MyCases = MyDB.OpenRecordset("Select * From tbl_Main")
While Not MyCases.EOF
' Count for Closed Date
If Not IsNull(MyCases![Closed Date]) Then
Set MyCaseCount = MyDB.OpenRecordset("SELECT CountRecords.* FROM
CountRecords WHERE [Assigned Specialist]='" & MyCases![Assigned Specialist] &
"' AND MyYear = " & Year(MyCases![Closed Date]))
If MyCaseCount.EOF Then
MyCaseCount.AddNew
MyCaseCount![Assigned Specialist] = MyCases![Assigned
Specialist]
MyCaseCount![MyYear] = Year(MyCases![Closed Date])
MyCaseCount!NumOfClosedCase = 1
Else
MyCaseCount.Edit
MyCaseCount!NumOfClosedCase =
Nz(MyCaseCount!NumOfClosedCase, 0) + 1
End If
MyCaseCount.Update
End If
' Count for 1st Reclosed Date
If Not IsNull(MyCases![1st Reclosed Date]) Then
Set MyCaseCount = MyDB.OpenRecordset("SELECT CountRecords.* FROM
CountRecords WHERE [Assigned Specialist]='" & MyCases![Assigned Specialist] &
"' AND MyYear = " & Year(MyCases![1st Reclosed Date]))
If MyCaseCount.EOF Then
MyCaseCount.AddNew
MyCaseCount![Assigned Specialist] = MyCases![Assigned
Specialist]
MyCaseCount![MyYear] = Year(MyCases![1st Reclosed Date])
MyCaseCount!NumOfClosedCase = 1
Else
MyCaseCount.Edit
MyCaseCount!NumOfClosedCase =
Nz(MyCaseCount!NumOfClosedCase, 0) + 1
End If
MyCaseCount.Update
End If
' Count for 2nd Reclosed Date
If Not IsNull(MyCases![2nd Reclosed Date]) Then
Set MyCaseCount = MyDB.OpenRecordset("SELECT CountRecords.* FROM
CountRecords WHERE [Assigned Specialist]='" & MyCases![Assigned Specialist] &
"' AND MyYear = " & Year(MyCases![2nd Reclosed Date]))
If MyCaseCount.EOF Then
MyCaseCount.AddNew
MyCaseCount![Assigned Specialist] = MyCases![Assigned
Specialist]
MyCaseCount![MyYear] = Year(MyCases![2nd Reclosed Date])
MyCaseCount!NumOfClosedCase = 1
Else
MyCaseCount.Edit
MyCaseCount!NumOfClosedCase =
Nz(MyCaseCount!NumOfClosedCase, 0) + 1
End If
MyCaseCount.Update
End If
MyCases.MoveNext
Wend
End Function