Calculate Percentage on Text Field

[Ayse via AccessMonster.com]

I am trying to come up with a percentage on 2 text fields.

I have two text fields [Insurance] and [Diagnosis]. I need to know what
percentage of the total records come from each different type of Insurance
and each different Diagnosis.

I created a Qry with the Group By then Count funtion but since
my fields are text I don't know what to do to come up with the avg.

 

[Guest]

Create a query, select all the fields from the table but the two fields
Insurance and Diagnosis convert to number
clng = convert to long
or
cdbl= convert to double
depend on the type of your data

then create another query based on the first query that is based on your
table only that this time the two fields are numbers.

 

[Gary Walter]

I am trying to come up with a percentage on 2 text fields.

I have two text fields [Insurance] and [Diagnosis]. I need to know what
percentage of the total records come from each different type of Insurance
and each different Diagnosis.

I created a Qry with the Group By then Count funtion but since
my fields are text I don't know what to do to come up with the avg.

Hi Ayse,

If I understand correctly, one way
is to use subqueries. Here be a
simple table to use as an example:

tblAyse Insurance Diagnosis
A one
A two
A three
B one
B one
B one
B three
C two
C two



In a groupby query, you can hide
any further groupby subqueries behind
an aggregate (like "Max" in the following
example).


SELECT
tblAyse.Insurance,
tblAyse.Diagnosis,
Count(*) AS cntgroup,
Max((SELECT Count(*) FROM tblAyse t1 )) AS TotRecs,
Max((SELECT Count(Insurance) FROM tblAyse t2 WHERE t2.Insurance = tblAyse.Insurance)) AS CntIns,
Max((SELECT Count(Diagnosis) FROM tblAyse t3 WHERE t3.Diagnosis = tblAyse.Diagnosis)) AS CntDiag,
Max((SELECT Count(Insurance) FROM tblAyse t2 WHERE t2.Insurance = tblAyse.Insurance)/(SELECT Count(*) FROM tblAyse t1 )) AS InsPerCent,
Max((SELECT Count(Diagnosis) FROM tblAyse t3 WHERE t3.Diagnosis = tblAyse.Diagnosis)/(SELECT Count(*) FROM tblAyse t1 )) AS DiagPerCent
FROM tblAyse
GROUP BY tblAyse.Insurance, tblAyse.Diagnosis;

qryAyse Insurance Diagnosis cntgroup TotRecs CntIns CntDiag InsPerCent DiagPerCent
A one 1 9 3 4 0.333333333333333 0.444444444444444
A three 1 9 3 2 0.333333333333333 0.222222222222222
A two 1 9 3 3 0.333333333333333 0.333333333333333
B one 3 9 4 4 0.444444444444444 0.444444444444444
B three 1 9 4 2 0.444444444444444 0.222222222222222
C two 2 9 2 3 0.222222222222222 0.333333333333333



I think the above is what you were
trying to produce.

gary